help with a mysql query

Question

i have a DB with all transactions of my online webshop, and im trying to make a query to print out a simple financial statement.

it will be printed in a table like this:

<th>month</th>
<th>number of sales</th>
<th>money in</th>
<th>money out</th>
<th>result</th>

the query that fails with: #1111 - Invalid use of group function

SELECT 
month(transaction_date) as month,
count(incoming_amount > '0') as number_of_sales,
sum(incoming_amount / 1.25) as money_in,
sum(outgoing_amount) as money_out,
sum((incoming_amount / 1.25) - sum(outgoing_amount)) as result
FROM myDB WHERE year(timestamp) = '2011' order by id desc");

Can anyone point me in the right direction?

Answer 1

SELECT 
month(transaction_date) as month,
sum(if(incoming_amount>0,1,0)) as number_of_sales,
sum(incoming_amount)/1.25 as money_in,
sum(outgoing_amount) as money_out,
sum((incoming_amount/1.25)-outgoing_amount) as result
FROM myDB 
WHERE timestamp>='2011-01-01 00:00:00' AND timestamp<='2011-12-11 23:59:59'
GROUP BY month;

1. you need to specify a column when using aggregate function
2. year(timestamp) does not make use on mysql index (if you have define an index on timestamp)
3. aggregate function on count(incoming_amount > '0') is incorrect
4. sum does not looks correct too