Why is a column invalid because it is not in an aggregate function or group by clause?

Question

I am trying to execute the following query in SQL Server:

Select *, sum(Quantity) as Quantity from tblDeal where buyercode = 25 
and dealdate >= '2014/04/01' and dealdate <= '2015/03/31' 
group by dealno order by DealDate, DealNo

and I am getting the following error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Column 'tblDeal.dealid' is invalid in the select list because it is not 
contained in either an aggregate function or the GROUP BY clause.

tbldeal.dealid is actually one of multiple columns present in the tbldeal table.

I have searched the internet for solutions but in most cases, people are suggesting to add the column to the group by clause or add an aggregate function.These maneuvers might remove the error but might affect the end result.My requirement is to keep the data grouped by dealno.What should I do? Kindly help.

You have to group by all the other columns in your query that do not have an aggregate applied (`SUM` in your example) `SELECT *..` — Milen, Nov 28 '14 at 10:40
possible duplicate of [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) — Tanner, Nov 28 '14 at 10:44
@MilenPavlov , Will this affect the end result, I mean will the data stay grouped by dealno ? — TVicky, Nov 28 '14 at 10:44

xlecoustillier · Accepted Answer · 2014-11-28T11:47:12.840

You can't SELECT * if you use aggregate functions, as you have to GROUP BY everything that is not enclosed in those functions (unless you GROUP BY all columns contained in *).

Remove the * and add only the relevant columns, and GROUP BY them:

SELECT DealNo, 
       col1, 
       col2, 
       DealDate,
       SUM(Quantity) AS Quantity 
FROM   tblDeal 
WHERE  buyercode = 25 
       AND DealDate >= '2014/04/01' 
       AND DealDate <= '2015/03/31' 
GROUP  BY DealNo, 
          col1, 
          col2 
          DealDate
ORDER  BY DealDate, 
          DealNo

If you want to only GROUP BY DealNo, you have to SELECT only DealNo apart from the aggregate functions, or use aggregate functions for the other columns too (as MAX or any other which fits your needs):

SELECT DealNo, 
       MAX(col1), 
       MAX(col2), 
       MAX(DealDate) AS dd,
       SUM(Quantity) AS Quantity 
FROM   tblDeal 
WHERE  buyercode = 25 
       AND DealDate >= '2014/04/01' 
       AND DealDate <= '2015/03/31' 
GROUP  BY DealNo
ORDER  BY dd, 
          DealNo

Let's say you have 2 rows like this:

DealNo | col1 | col2 | DealDate   | Quantity
     1 |    0 |    1 | 2014/04/02 |       10
     1 |    2 |    3 | 2014/04/02 |       10

If you only GROUP BY DealNo while selecting also col1 and col2, which values would you expect SQL Server to put into col1 and col2 results ?

Thanks for your answer, but I need all the columns to be included in the select clause. — TVicky, Nov 28 '14 at 10:45
Then you can't group by only dealno, or you'll have to aggregate all your other columns too (using MAX for instance). — xlecoustillier, Nov 28 '14 at 10:46

score 0 · Answer 2 · answered Nov 28 '14 at 11:22

You can also write as:

Select *,
       sum(Quantity) OVER ( PARTITION BY dealno ORDER BY dealno ASC) as Quantity 
from @tblDeal where buyercode = 25 
and dealdate >= '2014/04/01' and dealdate <= '2015/03/31' 
--group by dealno 
order by DealDate, DealNo

Why is a column invalid because it is not in an aggregate function or group by clause?

2 Answers2