Reputation: 399
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
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
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