Hi, try it.
*SELECT **e.name* <http://e.name/>*, **count(in.guest) as Guests**, **(SELECT count(presents) FROM watermelon.invitations WHERE presents = true and e.id= event) as Presents ** FROM** watermelon.events e** LEFT JOIN **watermelon.invitations in** ON * *e.id* <http://e.id/>* = **in.event** GROUP BY **in.event**;* 2013/4/5 Jose Martinez <[email protected]> > Hi, I have two tables, invitations and events. > > > <https://lh3.googleusercontent.com/-SI7MpdSuZss/UV4sJakvbiI/AAAAAAAAAJU/EUEbR3QJnPE/s1600/2Tablas.png> > > > > I need get the *name of the event*, the *total of guest per event*and the > *total of presents when its true per event* in the same query! > > > > <https://lh5.googleusercontent.com/-zK2nX4bgYGw/UV4qymB00FI/AAAAAAAAAJM/xnx6XlXk6IY/s1600/2Tablas.png> > Somthing like join both tables (left join) *selecting events.name, > count(invitations.guest), count(invitations.presents when = true)* and* group > it by events...* > * > * > > Looking the tables data... > > > <https://lh4.googleusercontent.com/-A57QIy6UXlM/UV43TcNEssI/AAAAAAAAAJk/tXcui2bUmU8/s1600/invitaciones.jpg> > > > <https://lh6.googleusercontent.com/-XW9nYrgel9Y/UV45AXyODHI/AAAAAAAAAJ8/MoY4v-IjXbM/s1600/eventos.jpg> > > > I think it could work but i dont get what I want...' > > *SELECT e.name, count(in.guest) as Guests, (SELECT count(presents) FROM > watermelon.invitations WHERE presents = true) as Presents > FROMwatermelon.events eLEFT JOIN watermelon.invitations > in ON e.id = in.event GROUP BY in.event;* > * > * > *Then I get:* > > > <https://lh4.googleusercontent.com/-R1xPr30sLKo/UV45ewKGvfI/AAAAAAAAAKE/tG_zKem7Zg8/s1600/resultado.jpg> > > I hope you understand and can help me! > > Some suggestion? Please I need it, and Im tired of trying and getting > wrong results... > > *Thanks in advance! * > * > * > * > * > > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- ----------- Ayataro Kogo -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
