Menu
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.
541
SUM() function with group by. SUM is used with a GROUP BY clause. The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group. It is better to identify each summary row by including the GROUP BY clause in the query resulst.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. GROUP BY Syntax. ORDER BY column_name(s);
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 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
SUM() function with group by. MySQL SUM() function retrieves the sum value of an expression which has undergone a grouping operation by GROUP BY clause. Example: Sample table: purchase. Code: SELECT cate_id,SUM(total_cost) FROM purchase GROUP BY cate_id;
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
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
