Keep receiving Error Not contained in either an aggregate function or the GROUP BY clause

Question

I keep receiving this error Column 'vStockSerialsTemp.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. ALTER VIEW vStockSerials as

    SELECT Id
         , StockOid
         , WarehouseOid
         , serial
         , StockCode
         , ActionPrice
         , StockName
         , StockTitle
         , Warehouse
         , SlipDate
         , ActionType
         , SlipType 
      FROM vStockSerialsTemp
     UNION ALL 
    SELECT Id
         , StockOid
         , WarehouseOid
         , serial
         , StockCode
         , sum(CASE WHEN ActionType = 'Income' THEN ActionPrice ELSE -ActionPrice END) as ActionPrice
         , StockName
         , StockTitle
         , Warehouse
         , SlipDate
         , SlipType
         , 'Balance' as ActionType
    FROM vStockSerialsTemp     
   GROUP BY
         serial
  HAVING sum(CASE WHEN ActionType = 'Expense' THEN ActionPrice ELSE -ActionPrice END) <> 0 


--ORDER BY
--    ActionType DESC
 GO

Honestly, googling with your question title will give you the solution. Have you tried that? — Szymon, May 08 '14 at 06:19
add rest of the columns in GROUP BY serial,Id,StockOid,WarehouseOid,serial,StockCode,StockName,StockTitle,Warehouse,SlipDate,SlipType,ActionType — mohan111, May 08 '14 at 06:20
For a query containing aggregate functions, all other columns must be in group by clause. So all the expressions in a select query, should be either aggregate functions or included in a group by clause. — Roopesh, May 08 '14 at 08:31

score 1 · Answer 1 · answered May 08 '14 at 06:22

In your second query of the UNION ALL you aggregate ActionPrice, thus you need to GROUP BY on your other fields (except ActionType as this is not a field from a table):

SELECT Id,StockOid,WarehouseOid,serial,StockCode,ActionPrice,StockName,
    StockTitle,Warehouse,SlipDate,ActionType,SlipType 
 FROM
    vStockSerialsTemp
UNION ALL 
SELECT 
    Id,StockOid,WarehouseOid,serial,StockCode,  
        sum(CASE WHEN ActionType = 'Income' 
                THEN ActionPrice ELSE -ActionPrice END) as ActionPrice,
    StockName,StockTitle,Warehouse,SlipDate,SlipType,  
    'Balance' as ActionType
FROM
    vStockSerialsTemp     
GROUP BY
    Id,StockOid,WarehouseOid,serial,StockCode,StockName,StockTitle,Warehouse,
        SlipDate,SlipType
HAVING
    sum(CASE WHEN ActionType = 'Expense' 
            THEN ActionPrice ELSE -ActionPrice END) <> 0

Even though it will fix the error, grouping by Id probably doesn't make much sense as nothing will be grouped. — Szymon, May 08 '14 at 06:26
Assuming `id` is the primary key (or at least unique), but given the fact that if it were there is no need for the aggregation you could also assume that it isn't. Looking at the SQL in more detail there are more fundamental issues at play (such as an uneven number of columns either side of the `UNION ALL`) so I'm not sure we can assume anything about the table structure. — Simon1979, May 08 '14 at 06:57

score 0 · Answer 2 · answered May 08 '14 at 06:31

you can aggregate only by "serial" using partition:

 SELECT Id,StockOid,WarehouseOid,serial,StockCode,ActionPrice,StockName,StockTitle,Warehouse,SlipDate,ActionType,SlipType 
 FROM
    vStockSerialsTemp
UNION ALL 
SELECT * from
(SELECT 
    Id,StockOid,WarehouseOid,serial,StockCode,  
    sum(CASE WHEN ActionType = 'Income' THEN ActionPrice ELSE -ActionPrice END) OVER(PARTITION BY serial) as ActionPrice,
    StockName,StockTitle,Warehouse,SlipDate,SlipType,  
    'Balance' as ActionType) x WHERE ActionPrice<>0


--ORDER BY
--    ActionType DESC
 GO

Keep receiving Error Not contained in either an aggregate function or the GROUP BY clause

2 Answers2