Is this what you're after? SELECT COUNT(*) AS members, SUM(IF(payment_type = 'creditcard', 1, 0)) AS pay_by_card FROM members JOIN payments USING (memno) GROUP BY group;
On 8/5/05, Russell Horn <[EMAIL PROTECTED]> wrote: > I'm having difficulty writing a query as follows. I'm unsure if I need a > subquery, a union or if this isn't actually possible without using > temporary tables. > > I have two tables for members. > > Table 1 (members) Table 2 (payments) > +----------+------------+ +---------+--------------+ > | memno | group | | memno | payment_type | > +----------+------------+ +---------+--------------+ > | 1 | a | | 1 | cash | > | 2 | b | | 2 | cash | > | 3 | a | | 3 | creditcard | > | 4 | a | | 4 | check | > | 5 | c | | 5 | creditcard | > ... ... > > > I'd like a query that returns the total number in each group, together > with the number paying by credit card. > > Obviously I can build two queries, and use a temporary table, but is > there a way to get a table like that below in a single query? > > +----------+------------+------------------+ > | group | members | pay_by_card | > +----------+------------+------------------+ > | a | 5 | 3 | > | b | 26 | 18 | > ... > > This will be using MySQL 5 if that helps > > Thanks! > > Russell > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
