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.
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 KrishnaYou live only once ..If you do it right once is enough....... |
|
|
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. |
|
|
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 |
|
|
MuralikrishnaVeera
Posting Yak Master
129 Posts |
Posted - 2014-04-24 : 09:12:03
|
Still not sure with Question .May be this will help Youdeclare @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 CTEAS(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 KrishnaYou live only once ..If you do it right once is enough....... |
|
|
|
|
|
|
|