CODEWITHSUNDEEP
sql-server
Nightmaresux
Nightmaresux

Reputation: 538

Ignore the null values in aggregation

i have table Regions :

Region Typee      Cnt
1        NEW      1
1        LEAVE    5
1        TOTAL    250
2        NEW      2
2        TOTAL    330
2        LEAVE    10

and i want to make a table that will look like this:

Region New Leave Total
1       1    5    250
2       2    10   330

i tried this: 

insert into     DP_CfCustCnt3             select Region,    
  case when Typee = 'NEW' then (select Cnt where typee = 'NEW') end as New,
 case when Typee = 'LEAVE' then (select Cnt where typee = 'LEAVE') end as Leave, 
 case when Typee = 'TOTAL' then (select Cnt where typee ='TOTAL') end as Total
 from Regions

but it gives me table like:

Region New Leave Total
1       1    NULL  NULL
1       NULL   5    NULL
1       NULL   NULL 250

Thank you for any advice.

Upvotes: 0

Views: 56

Answers (3)

DavidG
DavidG

Reputation: 118937

You can either pivot or expanding on your SQL:

insert into     DP_CfCustCnt3
SELECT Region,
        SUM(New) New,
        SUM(Leave) Leave,
        SUM(Total) Total
FROM (
SELECT  Region,
        CASE Typee WHEN 'NEW' THEN Cnt ELSE 0 END AS New,
        CASE Typee WHEN 'LEAVE' THEN Cnt ELSE 0 END AS Leave,
        CASE Typee WHEN 'TOTAL' THEN Cnt ELSE 0 END AS Total

FROM test) data
GROUP BY Region

Upvotes: 1

Code Different
Code Different

Reputation: 93151

What you described is a PIVOT function:

INSERT INTO DP_CfCustCnt3
    SELECT  pvt.Region,
            pvt.New,
            pvt.Leave,
            pvt.Total
    FROM    Regions r
    PIVOT   (
                SUM(Cnt) FOR Typee IN ([NEW],[LEAVE],[TOTAL])
            ) pvt

Upvotes: 1

mxix
mxix

Reputation: 3659

select
    REGION,
    SUM(CASE WHEN [TYPEE] = 'NEW' THEN CNT ELSE 0 END) [NEW],
    SUM(CASE WHEN [TYPEE] = 'LEAVE' THEN CNT ELSE 0 END) [LEAVE],
    SUM(CASE WHEN [TYPEE] = 'TOTAL' THEN CNT ELSE 0 END) [TOTAL]
FROM REGIONS
GROUP BY
    REGION

Upvotes: 0

Related Questions