Re: Query Combining tables

[Scott Noyes]

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]

Re: Query Combining tables

[Philippe Poelvoorde]

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]

Re: Query Combining tables

[Russell Horn]

Thanks for the two incredibly fast responses, they were perfect -
problem solved.

Russell.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]