CODEWITHSUNDEEP
sqlsql-server
Brasciole
Brasciole

Reputation: 377

SQL Query with SUM with Group By

I can't make my query work the way I need to. I have a simple query which outputs the following data:

enter image description here

What I need is to SUM the total_reseller_sales for each month and not display the same month twice. I try the following but it throws an error

SELECT rs.resellerid, rs.sid, SUM(rs.total_reseller_sales), s.month, s.sid 
FROM sales_report_resellers rs 
INNER JOIN resellers r ON rs.resellerid = r.resellerid 
INNER JOIN sales_report s ON rs.sid = s.sid 
WHERE (rs.sid > '294' AND rs.sid < '306') AND r.insidesales = 0 
  AND r.resellerid IN (7, 18, 22) 
GROUP BY month

The error I get is that each element in SELECT is invalid because it is not contained in either an aggregate function or the GROUP BY clause.

If I include them i nthe GROUP BY, then I get the same results.

Any help would be appreciated.

Upvotes: 24

Views: 87751

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

The GROUP BY clause forms rows for each unique combinaton of the columns you nominate in that clause.

If you want to show a sum for each month, then only include s.month in the group by clause: e.g

SELECT s.month, SUM(rs.total_reseller_sales)
FROM sales_report_resellers rs 
INNER JOIN resellers r ON rs.resellerid = r.resellerid 
INNER JOIN sales_report s ON rs.sid = s.sid 
WHERE (rs.sid > '294' AND rs.sid < '306') AND r.insidesales = 0 
  AND r.resellerid IN (7, 18, 22) 
GROUP BY s.month

If you include reseller details in the select clause also include them in the group by clause then there will be one row per reseller AND month

Upvotes: 29

Related Questions