December 12, 2011 at 11:45 am
I need to display count based on ID being either 4, 8 or 128.
something like below... I cant get it right 🙁 Please help
select
COUNT(case when t.Application_ID = 4 then 1 else 0 end) as 'AP Images',
COUNT(case when t.Application_ID = 8 then 1 else 0 end) as 'AP Exchange',
COUNT(case when t.Application_ID = 128 then 1 else 0 end) as 'AP Archive',
COUNT(case when t.Application_ID in (4,8) then t.Application_ID else 0 end) as 'AP Images and AP Exchange',
COUNT(case when t.Application_ID in (4,128) then t.Application_ID else 0 end) as 'AP Images and AP Archive',
COUNT(case when t.Application_ID in (8,128) then t.Application_ID else 0 end) as 'AP Images',
COUNT(case when t.Application_ID in (8,128,4) then t.Application_ID else 0 end) as 'AP Archive and AP Exchange'
from table t
or
select
case when t.Application_ID = 4 then 'AP Images'
when t.Application_ID = 8 then 'AP Exchange'
when t.application_id = 128 then 'AP Archive'
when t.application_id in (4,8) then 'AP Images and AP Exchange'
when t.application_id in (4,128) then 'AP Images and AP Archive'
when t.application_id in (8,128) then 'AP Archive and AP Exchange'
end as ApplicationName,
count(*) Count
from (
The result should be in the form
Count= Ap Images
Count= AP Exchange
Count= Ap Archive
Count= Ap Images and Archive and Exchange
Count= AP Images and Archive
Count= AP images and Exchange
Count= AP Archive and Exchange
December 12, 2011 at 11:50 am
Change "COUNT" to "SUM" and you should get what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 12:18 pm
but i want it PIVOT style... all the hadcoded column names you see there should be as rows with their count, ?
December 12, 2011 at 12:25 pm
How can I PIVOT this... All the columns and their count in two columns side by side.
select
sum(case when t.Application_ID = 4 then 1 else 0 end) as 'AP Images',
sum(case when t.Application_ID = 8 then 1 else 0 end) as 'AP Exchange',
sum(case when t.Application_ID = 128 then 1 else 0 end) as 'AP Archive',
sum(case when t.Application_ID in (4,8) then 1 else 0 end) as 'AP Images and AP Exchange',
sum(case when t.Application_ID in (4,128) then 1 else 0 end) as 'AP Images and AP Archive',
sum(case when t.Application_ID in (8,128) then 1 else 0 end) as 'AP Archive and AP Exchange',
sum(case when t.Application_ID in (8,128,4) then 1 else 0 end) as 'AP Images AP Archive AP Exchange'
from (
select a.Application_ID,
mo.MDMOrganizationName,
COUNT(*) count
from tbl_MDMOrganizations mo (Nolock)
inner join
(select o.mdmid, o.Organization_ID, o.OrganizationType_ID from tbl_Organizations o (Nolock)
inner join type_Organization t (Nolock) on o.OrganizationType_ID = t.OrganizationType_ID
---- Filter out anonymous and Internal
where t.OrganizationType_ID not in (0, 1) and o.MDMID is not null ) o
on mo.MDMID = o.MDMID and mo.Status = 1
inner join map_Organization_Application moa (Nolock)
on o.Organization_ID = moa.Organization_ID
inner join tbl_Applications a (Nolock)
on moa.Application_ID = a.Application_ID
and a.Application_ID in (4, 8, 128)
group by a.Application_ID, mo.MDMOrganizationName
) t
December 12, 2011 at 12:48 pm
Couple of options. You could use the T-SQL Pivot operator, as per http://msdn.microsoft.com/en-us/library/ms177410.aspx, or you could build a query for each count and use Union All to connect them into rows.
Personally, I hate the Pivot operator. Too crippled for most usefulness, and I also firmly believe pivoting and such should be done in the presentation layer, or at least the UI. After all, Excel does MUCH better pivoting than the database can do, and it can easily query database data for a pivot table/chart. So, I'd be inclined towards building a set of queries and using Union All. But your tastes may run different than mine and Pivot may appeal more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 1:20 pm
I agree with Gus on this one. Just to show you based on your example you could do something like this.
create table #Details
(
Application_ID int
)
go
insert #Details
select 4
go 8
insert #Details
select 8
go 23
insert #Details
select 128
go 13
select 'AP Images', (select sum(case when t.Application_ID = 4 then 1 else 0 end) as 'AP Images' from #Details t)
union all
select 'AP Exchange', (select sum(case when t.Application_ID = 8 then 1 else 0 end) as 'AP Exchange' from #Details t)
union all
select 'AP Archive', (select sum(case when t.Application_ID = 128 then 1 else 0 end) as 'AP Archive' from #Details t)
union all
select 'AP Images and AP Exchange', (select sum(case when t.Application_ID in (4,8) then 1 else 0 end) as 'AP Images and AP Exchange' from #Details t)
union all
select 'AP Images and AP Archive', (select sum(case when t.Application_ID in (4,128) then 1 else 0 end) as 'AP Images and AP Archive' from #Details t)
union all
select 'AP Archive and AP Exchange', (select sum(case when t.Application_ID in (8,128) then 1 else 0 end) as 'AP Archive and AP Exchange' from #Details t)
union all
select 'AP Images AP Archive AP Exchange', (select sum(case when t.Application_ID in (8,128,4) then 1 else 0 end) as 'AP Images AP Archive AP Exchange' from #Details t)
drop table #Details
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2011 at 2:40 pm
Thanks, That was perfect !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply