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?

Do you really have a table called myDB ?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2011-08-31 13:18:38 +00:00
Commented Aug 31, 2011 at 13:18 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 31, 2011 at 13:18

ajreal · Accepted Answer · 2011-08-31 13:21:04Z

2

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;

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

edited Aug 31, 2011 at 13:21

answered Aug 31, 2011 at 12:46

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Headshota · Accepted Answer · 2011-08-31 12:48:01Z

1

Add group by statement:

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' GROUP BY month order by id desc");

answered Aug 31, 2011 at 12:48

Headshota

21.4k13 gold badges63 silver badges82 bronze badges

Comments

Johan · Accepted Answer · 2011-08-31 13:08:59Z

1

Building on @ajreal's answer, you can speed this query up by reusing previously calculated values like so:

SELECT s.*,
       (s.money_in - s.money_out) as result 
FROM
  (
  SELECT 
    month(transaction_date) as month,
    /*  year(transaction_date) as year   */  
    sum(incoming_amount>0) as number_of_sales, -- true = 1, false = 0.
    sum(incoming_amount)/1.25 as money_in,
    sum(outgoing_amount) as money_out,
  FROM myDB 
  WHERE transaction_date BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 23:59:59'
  GROUP BY /*year,*/ month DESC;
  ) AS s

If you select beyond the year, uncomment the relevant sections.
Note you can add a DESC modifier to group by to get the most recent results first.

answered Aug 31, 2011 at 13:08

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Collectives™ on Stack Overflow

help with a mysql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related