Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count in Case Statement

Author  Topic 

SQLRohit
Starting Member

2 Posts

Posted - 2014-04-24 : 06:53:32
I have to count the number of Ideas and Markets here. Please help.

CASE WHEN Team IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') THEN 'Ideas' ELSE 'Markets' END

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-24 : 07:21:05
Query does not make sense unless you provide sample data ...What about 'ideas' and 'Markets' are they columns or else what?..Post sample data to get your question answered.


Thanks rohit....

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

SQLRohit
Starting Member

2 Posts

Posted - 2014-04-24 : 07:40:30
Team is a Column, and ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') are different rows in the column. If ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') these rows occur I am calling it as Ideas, if not, I am calling it as Markets. There are about 150 total records and the above query gives me 130 Ideas and 20 Markets. I need to count and display Ideas, Markets and also calculate the percentage of Ideas Vs. Markets.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-24 : 07:58:38
quote:
Originally posted by SQLRohit

Team is a Column, and ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') are different rows in the column. If ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') these rows occur I am calling it as Ideas, if not, I am calling it as Markets. There are about 150 total records and the above query gives me 130 Ideas and 20 Markets. I need to count and display Ideas, Markets and also calculate the percentage of Ideas Vs. Markets.



I did it like this:


declare @t table(Team varchar(100))
insert into @t values
('Development/Deployment Project'), ('Deployment Fixed Team'), ('Development Fixed Team'), ('Non Fixed Team'),
('Markets one'), ('Markets2')

SELECT CountIdeas, CountMarkets, cast(100.0 * CountIdeas / CountMarkets as Numeric(10,2))
FROM (
SELECT
COUNT(CASE
WHEN Team IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team')
THEN 1
END) as CountIdeas
, COUNT(CASE
WHEN Team NOT IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team')
THEN 1
END) as CountMarkets
FROM @t
) t


Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-24 : 09:12:03
Still not sure with Question .May be this will help You



declare @t table(Team varchar(100))
insert into @t values
('Development/Deployment Project'), ('Deployment Fixed Team'), ('Development Fixed Team'), ('Non Fixed Team'),
('Markets one'), ('Markets2')
;WITH CTE
AS
(SELECT Team,(CASE WHEN Team IN('Development/Deployment Project',
'Deployment Fixed Team'
,'Development Fixed Team'
)THEN 'Ideas' ELSE 'Markets' END) AS Value FROM @t
)
SELECT COUNT(Value) AS [Count] FROM CTE GROUP BY Value
--SELECT Team,(CASE WHEN Value = 'Ideas' THEN (SELECT COUNT(*) FROM CTE WHERE Value ='Ideas') ELSE (SELECT COUNT(*) FROM CTE WHERE Value ='Markets') END) FROM CTE



---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -