CODEWITHSUNDEEP
sqlsql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3301

Use column of type bit to differentiate group by?

I have data that looks like the following:

Cell    Date       Hr  Min   Value  Kpi     Exists
CELL1   20141112   8   45    1      KPI1    NULL
CELL1   20141112   8   45    2      KPI1    NULL
CELL1   20141112   8   45    7      KPI1    NULL
CELL1   20141112   8   45    7      KPI1    NULL
CELL2   20141112   8   0     5      KPI2    1
CELL2   20141112   8   15    2      KPI2    1
CELL2   20141112   8   30    9      KPI2    1
CELL2   20141112   8   45    7      KPI2    1
CELL3   20141112   8   0     1      KPI3    1
CELL3   20141112   8   15    3      KPI3    1
CELL3   20141112   8   30    4      KPI3    NULL
CELL3   20141112   8   45    6      KPI3    1

I would like to group by Cell, Date, HR, KPI in order to get SUM(Value), but I would like to somehow group column [Exists] so that if there's a NULL in the grouping, it will somehow differentiate the ones with NULLs from the ones without NULLs. In other words, if there's a NULL in any of the 4 quarters, then show it.

For example, the result would look something like this:

CELL3   20141112   8    14   NULL
CELL2   20141112   8    23   1
CELL1   20141112   8    17   NULL

This is the query I'm currently using to group the data, which works:

create table #temp
(
 Cell varchar(10),
 Date int,
 Hr   int,
 Min  int,
 Value int,
 Kpi  varchar(10),
 [Exists] bit
)
;
insert into #temp values ('CELL1' ,  20141112 ,  8 ,  45  ,  1    ,  'KPI1'  ,  NULL)
insert into #temp values ('CELL1' ,  20141112 ,  8 ,  45  ,  2    ,  'KPI1'  ,  NULL)
insert into #temp values ('CELL1' ,  20141112 ,  8 ,  45  ,  7    ,  'KPI1'  ,  NULL)
insert into #temp values ('CELL1' ,  20141112 ,  8 ,  45  ,  7    ,  'KPI1'  ,  NULL)
insert into #temp values ('CELL2' ,  20141112 ,  8 ,  0   ,  5    ,  'KPI1'  ,  1   )
insert into #temp values ('CELL2' ,  20141112 ,  8 ,  15  ,  2    ,  'KPI1'  ,  1   )
insert into #temp values ('CELL2' ,  20141112 ,  8 ,  30  ,  9    ,  'KPI1'  ,  1   )
insert into #temp values ('CELL2' ,  20141112 ,  8 ,  45  ,  7    ,  'KPI1'  ,  1   )
insert into #temp values ('CELL3' ,  20141112 ,  8 ,  0   ,  1    ,  'KPI1'  ,  1   )
insert into #temp values ('CELL3' ,  20141112 ,  8 ,  15  ,  3    ,  'KPI1'  ,  1   )
insert into #temp values ('CELL3' ,  20141112 ,  8 ,  30  ,  4    ,  'KPI1'  ,  NULL)
insert into #temp values ('CELL3' ,  20141112 ,  8 ,  45  ,  6    ,  'KPI1'  ,  1   )


SELECT             
Cell
, Date            
, Hr            
,sum(KPI1) as 'KPI1'            
FROM #temp
PIVOT (SUM(Value)             
FOR kpi in             
(KPI1) ) AS pvt               
GROUP BY Cell, 
Date,             
hr

Upvotes: 0

Views: 104

Answers (1)

jpw
jpw

Reputation: 44931

Try this: case when min(coalesce([Exists],0)) = 0 then null else 1 end as [Exists]

The full query:

SELECT             
    Cell
   ,Date            
   ,Hr            
   ,sum(KPI1) as 'KPI1'
   ,case when min(coalesce([Exists],0)) = 0 then null else 1 end as [Exists]
FROM #temp
PIVOT (SUM(Value) FOR kpi in (KPI1)) AS pvt               
GROUP BY Cell, Date, hr

With your sample data the output is:

Cell       Date        Hr          KPI1        Exists
---------- ----------- ----------- ----------- -----------
CELL1      20141112    8           17          NULL
CELL2      20141112    8           23          1
CELL3      20141112    8           14          NULL

Upvotes: 2

Related Questions