MySQL Fields / Amount

Question

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

Here is a snippet of the query I'm having problems with. These sum and count commands process all the data in the table, 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 details since I don't have time. If you need more information, just ask.

Greetings.

UPDATE: I posted more information about the layout and the results I get here: www.conorhackett.com/sql/transaction.html

The problem is that when I join the payment_table (so I can specify the package_name_name instead of id), it counts all transactions for cash / checks. The date limit seems to be disappearing.

Any help was appreciated.

Answer 1

No correlated subqueries needed, 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

Then you can join the result with payment_typeif you need to get type names there.