I dont understand why "it is not contained in either an aggregate function or the GROUP BY clause." error

Question

Query ;

SELECT  ch.CharID,ch.CharName16, it.OptLevel, obj.ReqLevel1, item.ItemClass  
FROM _Items as it            
LEFT JOIN [dbo].[_Inventory] as inv ON it.ID64 = inv.ItemID   
LEFT JOIN [dbo].[_Char] as ch ON inv.CharID = ch.CharID           
LEFT JOIN [dbo].[_RefObjCommon] as obj ON it.RefItemID = obj.ID           
LEFT JOIN [dbo].[_RefObjItem] as item ON obj.Link = item.ID           
LEFT JOIN [dbo].[_BindingOptionWithItem] as adv ON it.ID64 = adv.nItemDBID            
     WHERE ch.CharName16 IS NOT NULL 
       AND CodeName128 NOT LIKE '%stone%' 
       AND CharName16 NOT LIKE '%]%'
       AND inv.Slot < 7

Query is working fine but ;

INSERT INTO ItemPoints (
     CharID
    , CharName16
    , OptLevel
    , ReqLevel1
    , ItemClass
    , TotalPoint
    )
SELECT  CharID
    , CharName16
    , SUM(OptLevel) AS OptLevel
    , SUM(ReqLevel1) AS ReqLevel1
    , SUM(ItemClass) AS ItemClass
    , SUM(OptLevel) + SUM(ReqLevel1) + SUM(ItemClass) AS TotalPoint
FROM (
    SELECT  ch.CharID, ch.CharName16, it.OptLevel, obj.ReqLevel1, item.ItemClass  
      FROM _Items as it            
      LEFT JOIN [dbo].[_Inventory] as inv ON it.ID64 = inv.ItemID   
      LEFT JOIN [dbo].[_Char] as ch ON inv.CharID = ch.CharID           
      LEFT JOIN [dbo].[_RefObjCommon] as obj ON it.RefItemID = obj.ID           
      LEFT JOIN [dbo].[_RefObjItem] as item ON obj.Link = item.ID           
      LEFT JOIN [dbo].[_BindingOptionWithItem] as adv ON it.ID64 = adv.nItemDBID            
     WHERE ch.CharName16 IS NOT NULL 
       AND CodeName128 NOT LIKE '%stone%' 
       AND inv.Slot < 7           
   ) tbl
GROUP BY CharName16

But sql server give this error ;

Column 'tbl.CharID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I dont understand this error and how can I fix?

Did you leave out the `AND CharName16 NOT LIKE '%]%'` on purpose in the `INSERT`? — NickyvV, Mar 19 '15 at 10:32
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:39

A_Sk · Accepted Answer · 2015-03-19T10:39:17.500

use..

GROUP BY CharID,CharName16

And try.

    INSERT INTO ItemPoints (
         CharID
        , CharName16
        , OptLevel
        , ReqLevel1
        , ItemClass
        , TotalPoint
        ) SELECT  ch.CharID, ch.CharName16, SUM(it.OptLevel), SUM(obj.ReqLevel1), SUM(item.ItemClass),
(SUM(it.OptLevel)+ SUM(obj.ReqLevel1) + SUM(item.ItemClass) ) total
          FROM _Items as it            
          LEFT JOIN [dbo].[_Inventory] as inv ON it.ID64 = inv.ItemID   
          LEFT JOIN [dbo].[_Char] as ch ON inv.CharID = ch.CharID           
          LEFT JOIN [dbo].[_RefObjCommon] as obj ON it.RefItemID = obj.ID           
          LEFT JOIN [dbo].[_RefObjItem] as item ON obj.Link = item.ID           
          LEFT JOIN [dbo].[_BindingOptionWithItem] as adv ON it.ID64 = adv.nItemDBID            
         WHERE ch.CharName16 IS NOT NULL 
           AND CodeName128 NOT LIKE '%stone%' 
           AND inv.Slot < 7   group By  ch.CharID, ch.CharName16

Oh my god. Is this really easy :D Thanks. – KaanKaragozoglu Mar 19 '15 at 10:31 — KaanKaragozoglu, Mar 19 '15 at 10:31

score 0 · Answer 2 · answered Mar 19 '15 at 10:31

0

You must add all values to group byclause wich is used in select clause without aggregation function so your group by must be:

GROUP BY CharID,CharName16

answered Mar 19 '15 at 10:31

Jens

67,715
15
98
113

I dont understand why "it is not contained in either an aggregate function or the GROUP BY clause." error

2 Answers2