MySQL count/sum fields

Question

Hi There,

What I am trying to achieve is a report on daily financial transactions. With my SQL query I would like to count the total number of cash transactions, the total cash value and the same for checks. I only want to do this for a specified date.

Here is a snippet of the query that I am having trouble with. These sum and count commands are processing all the data in the table and not for the selected date.

(SELECT SUM(amount) FROM TRANSACTION WHERE payment_type.name = 'cash') AS total_cash,
(SELECT COUNT(*) FROM TRANSACTION WHERE payment_type.name = 'cash') AS total_cash_transactions

Sorry if I havent posted enough detail as I haven't time. If you need more info just ask..

Cheers.

UPDATE: I have posted some more info on the layout and results i'm getting here: www.conorhackett.com/sql/transaction.html

The problem is that when I join the payment_table (so I can specify payment_type_name instead of id) It counts all the transactions for cash/cheque. The date limitation seems to disappear.

Any help appreciated.

Answer 1

No need for correlated subqueries, just use group by

select
 t.payment_type_id as type_id
,sum(t.amount) as total_cash
,count(*) as total_cash_transactions
from TRANSACTION t
where t.date = '2010-05-01'
group by t.payment_type_id

You can then join the result with payment_type if you need to get the type names from there.