CODEWITHSUNDEEP
sql-servergroup-by
TVicky
TVicky

Reputation: 399

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

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.

Upvotes: 2

Views: 4345

Answers (2)

xlecoustillier
xlecoustillier

Reputation: 16361

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 ?

Upvotes: 4

Deepshikha
Deepshikha

Reputation: 10284

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

Upvotes: 0

Related Questions