October 28, 2008 at 9:41 pm
plz help me out in sorting the way ....
create table #abc
(
source varchar(100,
date datetime,
status
)
insert into #'abc' values ('abc','10/02/2008','out')
insert into #'abc' values ('gh','7/02/2008','out')
insert into #'abc' values ('mo','7/02/2008','in')
insert into #'abc' values ('kl','08/02/2008','in')
insert into #'abc' values ('sri','10/02/2008','cancel')
insert into #'abc' values ('abc','9/02/2008','out')
insert into #'abc' values ('sri','10/02/2008','cancel')
insert into #'abc' values ('abc','10/02/2008','out')
insert into #'abc' values ('vw','7/02/2008','out')
insert into #'abc' values ('sri','10/02/2008','cancel')
insert into #'abc' values ('vw','9/02/2008','out')
insert into #'abc' values ('vw','10/02/2008','cancel')
insert into #'abc' values ('mo','10/02/2008','out')
insert into #'abc' values ('vw','7/02/2008','out')
select isnull(id,'total'),sum(case when status = 'out' then 1 else 0) as out,
sum(case when status = 'in' then 1 else 0) as [in],
sum(case when status = 'cancel' then 1 else 0 ) as cancel
from #abc
group by id
with rollup
order by 1
the output i get ...
id out in cancel
abc 3 0 0
gh 1 0 0
kl 0 1 0
mo 1 1 0
sri 0 0 3
total 8 2 4
vw 3 0 1
i need to get the total at the end and i need to order the count by desc for out in this fashion by desc ....
id out in cancel
abc 3 0 0
vw 3 0 1
gh 1 0 0
kl 0 1 0
mo 1 1 0
sri 0 0 3
total 8 2 4
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 28, 2008 at 11:30 pm
[font="Courier New"]SELECT
ISNULL(source,'total') Source,
SUM(CASE WHEN status = 'out' THEN 1 ELSE 0 END) AS out,
SUM(CASE WHEN status = 'in' THEN 1 ELSE 0 END) AS [in],
SUM(CASE WHEN status = 'cancel' THEN 1 ELSE 0 END) AS cancel,
ColOrder = CASE WHEN source IS NULL THEN 2 ELSE 1 END
FROM #abc
GROUP BY source
WITH rollup
ORDER BY ColOrder, Source[/font]
October 28, 2008 at 11:42 pm
Thanks Gardain for ur reply i dont have access now i shall check it out tomm and let u know...
but i have a doubt this query would make the total go down but is there a chance that other than total the counts of others ordered in descending.....
i mean the total will be at the bottom and the source will be ordered based on the counts in desc order other than total....is there any way ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
October 29, 2008 at 5:31 am
The solution Seth has provided works fine. You can alter it a bit by moving the CASE from 'Colorder' to the ORDER BY if you don't want to see this column in your results.
SELECT
ISNULL(source,'total') Source,
SUM(CASE WHEN status = 'out' THEN 1 ELSE 0 END) AS out,
SUM(CASE WHEN status = 'in' THEN 1 ELSE 0 END) AS [in],
SUM(CASE WHEN status = 'cancel' THEN 1 ELSE 0 END) AS cancel
-- ColOrder = CASE WHEN source IS NULL THEN 2 ELSE 1 END
FROM #abc
GROUP BY source
WITH rollup
ORDER BY CASE WHEN source IS NULL THEN 2 ELSE 1 END, Source
The sample you give as your requested results does not have a correct order. You say you want it ordered by [source] (except for row 'Total'), but you give value 'vw' as second row. Your results look like it's ordered by [out] desc and [source], but then the rows with values 'mo' and 'kl' are not correct.
October 29, 2008 at 7:39 am
Hi, please note there is a grouping function you should use to check whether the row is a total, not isnull function.
create table #abc
(
source varchar(100),
date datetime,
status varchar(100)
)
insert into #abc values ('abc','10/02/2008','out')
insert into #abc values ('gh','7/02/2008','out')
insert into #abc values ('mo','7/02/2008','in')
insert into #abc values ('kl','08/02/2008','in')
insert into #abc values ('sri','10/02/2008','cancel')
insert into #abc values ('abc','9/02/2008','out')
insert into #abc values ('sri','10/02/2008','cancel')
insert into #abc values ('abc','10/02/2008','out')
insert into #abc values ('vw','7/02/2008','out')
insert into #abc values ('sri','10/02/2008','cancel')
insert into #abc values ('vw','9/02/2008','out')
insert into #abc values ('vw','10/02/2008','cancel')
insert into #abc values ('mo','10/02/2008','out')
insert into #abc values ('vw','7/02/2008','out')
select
case grouping(source)
when 1 then 'total'
else source
end [Id],
isnull(nullif(grouping(source), 1), 0) [Total],
sum(case [status] when 'out' then 1 else 0 end) as [out],
sum(case [status] when 'in' then 1 else 0 end) as [in],
sum(case [status] when 'cancel' then 1 else 0 end) as cancel
from #abc
group by source
with rollup
order by grouping(source), source
drop table #abc
October 29, 2008 at 7:45 am
1976 (10/29/2008)
Hi, please note there is a grouping function you should use to check whether the row is a total, not isnull function.
And I keep on learning new stuff 😉
October 29, 2008 at 8:50 am
hansi really good stuff ...thkz bro
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 1, 2008 at 9:58 am
Hansi and Garadin,
Nice! thanks.
Are you able to produce a modification to the code which puts a separating line above the Total row? (between the data and the last row showing the totals?) like the following:
fobar data <-----data
forba2 data
------------ <-----separating line
total datatotal <-----row total as produced by your code
-uman
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply