August 18, 2008 at 9:04 am
i have a table T1 with column name Method and other column name status
so in method i have like
method---status
abc ---success
def ---success
abc---failure
ghi ---failure
def----success
abc---success
so what i need in the output is
Count
abc---success---2
abc--failure--------1
def----success---2
def----failure ----0
ghi---succes----0
ghi----failure----1
but when i am writing the query as
select method,status,count(*) from table t1
GROUP BY method,status
i am getting as
abc---success---2
abc--failure--------1
def----success---2
--------------------def----failure ----0----I am not getting these two rows how can i get that plz let meknow any alternative...
------------------------ghi---succes----0
ghi----failure----1
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 18, 2008 at 9:48 am
if there are no rows in your table for def----failure and ghi---succes then it will not be able to cout these at all.
do you have a master table that contains all the expected rows?
August 19, 2008 at 5:41 am
you can use the following query for the same:
create table arjun_temp(method varchar(20), success varchar(20))
insert into arjun_temp select
'bank','success'
insert into arjun_temp select
'bank','failure'
insert into arjun_temp select
'finance','success'
insert into arjun_temp select
'finance','success'
insert into arjun_temp select
'finance','success'
declare @y table(val varchar(20))
insert into @y select 'failure'
insert into @y select 'success'
select method, val, sum(case when val = success then 1 else 0 end) coun
from @y x cross join arjun_temp y
group by method, val
order by 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply