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:

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.

1 Answer

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