Aggregation with Group by

Question

I have the following sql statement:

SELECT MODEL, COLOR, SUM(SALES)
FROM SALES
WHERE MODEL='chevy'
GROUP BY MODEL;

When I run this statement, I have an error message saying " "sales.color" must appear in the GROUP BY clause or be used in an aggregate function"

I don't understand why COLOR has to appear in Group by or used in aggregate function.

Thanks.

score 3 · Accepted Answer · edited Sep 27 '14 at 18:42

3

Color must appear in the group by because you are doing an aggregate select. Essentially the problem here is it doesn't know what rows to sum the sales across. You are selecting out the sum of sales for each model and the color of each model so you either need to remove the color from the selected columns if you want to know the sales for each model regardless of color, or add color to the group by if you want to know the sales for each color of each model.

In the first case:

SELECT MODEL, SUM(SALES)
FROM SALES
WHERE MODEL='chevy'
GROUP BY MODEL;

In the second case:

SELECT MODEL, COLOR, SUM(SALES)
FROM SALES
WHERE MODEL='chevy'
GROUP BY MODEL,COLOR;

edited Sep 27 '14 at 18:42

answered Sep 27 '14 at 18:39

krobertson

91
2

Thanks for your explanation. It seems that group by specifies the rows for aggregation, not those columns in select. Is my understanding correct? – Zack Sep 27 '14 at 18:49
Yes the group by will "group" all of the rows together whose values are equal for the given column. If you group by MODEL, it will group all of the rows with equal MODEL together. – krobertson Sep 27 '14 at 18:55
Similar question here: [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e?lq=1). The selected answer gives an example. – krobertson Sep 27 '14 at 19:09

score 2 · Answer 2 · edited May 23 '17 at 10:34

2

SELECT MODEL, COLOR, SUM(SALES)
FROM SALES
WHERE MODEL='chevy'
GROUP BY MODEL,COLOR;

SQL Server: Difference between PARTITION BY and GROUP BY

edited May 23 '17 at 10:34

Community

1
1

answered Sep 27 '14 at 18:34

Diego Farina

339
2
10

This doesn't explain *why*. – James Binford Sep 27 '14 at 18:35
@JamesBinford I added i link – Diego Farina Sep 27 '14 at 18:39

score 1 · Answer 3 · answered Sep 27 '14 at 18:35

This is a Rule of Groupby:

Rule 1: If a select block does have a GROUP BY clause, any column specification specified in the SELECT clause must exclusively occur as a parameter of an aggregated function or in the list of columns given in the GROUP BY clause, or in both.

There are more here: http://www.informit.com/articles/article.aspx?p=664143&seqNum=6

score 0 · Answer 4 · answered Sep 27 '14 at 18:37

0

Use:

GROUP BY MODEL,COLOR

Since you are asking for MODEL and COLOR fields in query and applying aggregation on SALES based on these two fields you have to GROUP the aggregation using both of these fields

answered Sep 27 '14 at 18:37

Murtaza Zaidi

599
3
14

Aggregation with Group by

4 Answers4