SQL Query with SUM with Group By

Question

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.

Well, if you don't want to display the same month twice, then what are you supposed to display when two resellerIDs exist in one month? — ZLK
– ZLK, Commented Jan 23, 2017 at 22:33
I believe you need to have Having instead of where when working with functions such as SUM, COUNT, MIN, etc... — user6646201
– user6646201, Commented Jan 23, 2017 at 22:35
You can't show resellerid together of your sum, because you haven't put it in group by clause. The same for rs.sid and s.sid. You must to change approach for your query and fields list — Joe Taras
– Joe Taras, Commented Jan 23, 2017 at 22:37
I can imagine a query returns these fields: SUM(rs.total_reseller_sales), s.month, rs.sid (with group by as follow s,month, rs.sid) — Joe Taras
– Joe Taras, Commented Jan 23, 2017 at 22:38
If you want to total up the sales, then you have to remove the reseller id from your SELECT (because multiple sellers will have sales in any given month). If you only want a total of sales for a given month, then stop trying to break it out by reseller. Just SELECT month and SUM(total_reseller_sales). — pmbAustin
– pmbAustin, Commented Jan 23, 2017 at 22:45

Paul Maxwell · Accepted Answer · 2017-01-24 03:28:14Z

30

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

edited Jan 24, 2017 at 3:28

answered Jan 23, 2017 at 23:00

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

2 Comments

Brasciole Over a year ago

works perfectly. I guess you don't have to add rs.total_reseller_sales in the clause because of the SUM?

stevestar888 Over a year ago

Thanks so much! My issue was I had GROUP BY ... before the INNER JOIN.

Collectives™ on Stack Overflow

SQL Query with SUM with Group By

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related