Case Statement

[krotha]

I have query like this

select t.Id,case when t.type ='19' then 'A1'
when t.type ='22' then 'A2'
else 'A3' End Actual_Type,count(t.type) record_count
from temp t
group by Id,t.type

Gives me results like this
ID Actual_type record_count
1 'A1' 1
1 'A3' 1

But I want records like this
ID Actual_type record_count
1 'A1' 1
1 'A2' 0
1 'A3' 1

for t.type = '22' there is no matching record in the table, but I want to return a record with 0 as value.

 

[gmmastros]

Hmmmm...

If I understand correctly, each ID in the temp table should have 3 records (A1, A2, And A3). I've posted some code below, but it 'hard codes' the list of types as A1, A2, and A3. There's probably a better way, but for now, try this query to see if it works.

Select 	AllIdAndType.Id, AllIdAndType.Type, IsNull(Record_Count, 0) As RecordCount
From	(
		Select Id, Type
		From	(Select 'A1' As Type Union Select 'A2' Union Select 'A3') As AllTypes,
				(Select Distinct Id From @Temp) As T
		) As AllIdAndType
		Left outer Join (
					select t.Id,case when t.type ='19' then 'A1'
					            when t.type ='22' then 'A2'
					            else 'A3' End Actual_Type,count(t.type) record_count
					from temp t
					group by Id,t.type
				  ) As Data On AllIdAndType.Type = Data.Actual_Type And AllIdAndType.Id = Data.Id

This is the part that is weird (at least to me). First, I create a table (sort of) to hold the hard coded list of types, and a distinct list of id's. Once you have that list, a left join with your original sql returns the data you expect.

Select Id, Type
From    (Select 'A1' As Type Union Select 'A2' Union Select 'A3') As AllTypes,
        (Select Distinct Id From @Temp) As T

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

If the number of actual_types are less then try this:

select a.id, a.actual_type, sum(a.record_count)
from (select    Id,
          case when type ='19' then 'A1'
               when type ='22' then 'A2'
               else 'A3' End Actual_Type,
          count(type) record_count
from      temp 
group by  Id,
          type
union
select id, 'A1' Actual_Type, 0 record_count from temp
union  
select id, 'A2' Actual_Type, 0 record_count from temp
union
select id, 'A3' Actual_Type, 0 record_count from temp) a
group by a.id, a.actual_type

Regards,
AA