Russell Horn 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 |
..
something like this should do it :
SELECT group, count(*) as members, sum(
IF(payment_type='creditcard',1,0)) as pay_by_card
FROM members, payments
WHERE members.memno=payments.memno
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
