CODEWITHSUNDEEP
sql
user3112213
user3112213

Reputation: 3

Group by SQL not grouping

I have this query that works fine, except that it brings 2 records. It can make the grouping. I have 2 different customers (CUSTMANE) -- it looks this is why, and that's why I added the case.

Any thoughts on how to group so I can get the sum?

SELECT 

sum (INVOICEAMOUNT) as INVOICEAMOUNT ,
sum (PROFIT) as PROFIT,

ITEM,
ITEMNAME,
DATEADD(week, DATEDIFF(week, 6,  DATE), 6 + 7) as [Week],

case CUSTNAME
 when (select CUSTNAME where CUSTNAME like ('B%')) then 'Cust B'
 when (select CUSTNAME where CUSTNAME like ('H%')) then 'Cust H'
end as CUSTNAME,

case ITEMGROUP
 when (select ITEMGROUP where ITEMGROUP in ('CEM','SPECIAL')) then 'C & S'
 when (select ITEMGROUP where ITEMGROUP in ('CMH','CMG')) then 'C & C'
end as ITEMGROUP

FROM EXTRACT
 where DATE > = DATEADD(week,-5,GETDATE())
 and ID in ('230') 
 group by  

ITEM,
ITEMNAME,
CUSTNAME,
ITEMGROUP,
DATE

Upvotes: 0

Views: 305

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

SELECT  SUM(INVOICEAMOUNT) AS INVOICEAMOUNT
      , SUM(PROFIT) AS PROFIT
      , ITEM
      , ITEMNAME
      , DATEADD(week, DATEDIFF(week, 6, DATE), 6 + 7) AS [Week]
      , CASE WHEN CUSTNAME LIKE 'B%' THEN 'Cust B'
             WHEN CUSTNAME LIKE 'H%' THEN 'Cust H'
        END AS CUSTNAME
      , CASE WHEN ITEMGROUP IN ( 'CEM', 'SPECIAL' ) THEN 'C & S'
             WHEN ITEMGROUP IN ( 'CMH', 'CMG' ) THEN 'C & C'
        END AS ITEMGROUP
FROM    EXTRACT
WHERE   DATE > = DATEADD(week, -5, GETDATE())
        AND ID IN ( '230' )
GROUP BY ITEM
      , ITEMNAME
      , CASE WHEN CUSTNAME LIKE 'B%' THEN 'Cust B'
             WHEN CUSTNAME LIKE 'H%' THEN 'Cust H'
        END
      , CASE WHEN ITEMGROUP IN ( 'CEM', 'SPECIAL' ) THEN 'C & S'
             WHEN ITEMGROUP IN ( 'CMH', 'CMG' ) THEN 'C & C'
        END
      , DATEADD(week, DATEDIFF(week, 6, DATE), 6 + 7)

Upvotes: 1

Related Questions