Help with count - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with count

Author

Topic

ino mart
Starting Member

12 Posts

Posted - 2011-10-05 : 05:07:07

All

I have a table with next fields
Country, City, M1, M2, M3

M1, M2 and M3 contain a number between 1 and 100.

For each M I need to count how many records are above 90 and how many records are between 75 and 90.

So the result must be somehting like


Country|City|M1>90|M1>75)|M2>90|M2>75|M3>90|M3>75
A      |a   |   5 |   2  |  4  |  2  |  5  | 
A      |b   |  20 |      |  6  |  3  |     | 16
B      |a   |   7 |   1  |     |  8  |  2  | 13
B      |b   |     |   8  |  2  |  9  |  1  |  7

Anyone an idea how to do this? I already tried with next code, but this does not work as expected (when I remove AND COUNT(M1) < 0.90 I do get everything higher as 0.70 (but of course only for M1)

SELECT Country, City, COUNT(M1) AS Month1
FROM         dbo.X
GROUP BY Country, City
HAVING      (COUNT(M1) > 0.70 AND COUNT(M1) < 0.90)
ORDER BY Country, City

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 05:19:20

[code]
SELECT Country, City,
COUNT(case when M1 > 90 then M1 end) AS [M1>90],
COUNT(case when M1 > 75 and M1 <= 90 then M1 end) AS [M1>75],
COUNT(case when M2 > 90 then M2 end) AS [M2>90],
COUNT(case when M2 > 75 and M2 <= 90 then M2 end) AS [M2>75],
. . .
FROM dbo.X
GROUP BY Country, City
ORDER BY Country, City
[/code]

EDIT : didn't notice the between 75 and 90 requirement

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 05:20:42

[code]
SELECT Country, City,
COUNT(CASE WHEN M1>0.90 THEN M1 ELSE NULL END) AS [M1>90],
COUNT(CASE WHEN M1>0.75 AND M1 < 0.90 THEN M1 ELSE NULL END) AS [M1>75],
COUNT(CASE WHEN M2>0.90 THEN M2 ELSE NULL END) AS [M2>90],
COUNT(CASE WHEN M2>0.75 AND M2 < 0.90 THEN M2 ELSE NULL END) AS [M2>75],
COUNT(CASE WHEN M3>0.90 THEN M3 ELSE NULL END) AS [M3>90],
COUNT(CASE WHEN M3>0.75 AND M3 < 0.90 THEN M2 ELSE NULL END) AS [M3>75]
FROM dbo.X
GROUP BY Country, City
ORDER BY Country, City
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ino mart
Starting Member

12 Posts

Posted - 2011-10-05 : 05:33:35

thanks, this SQL-statement does exactly what I need. Topic can be closed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 05:43:53

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ino mart
Starting Member

12 Posts

Posted - 2011-10-05 : 07:35:11

I have an additional question about this topic. Is it also possible to extend this table with grand totals?

Country|City|M1>90|M1>75)|M2>90|M2>75|M3>90|M3>75
A      |a   |   5 |   2  |  4  |  2  |  5  | 
A      |b   |  20 |      |  6  |  3  |     | 16
B      |a   |   7 |   1  |     |  8  |  2  | 13
B      |b   |     |   8  |  2  |  9  |  1  |  7

GRAND TOTAL |  32 |  11  | 12  |  22 |  8  | 36

regards
Ino

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 07:47:37

you can . use


SELECT Country, City, 
COUNT(CASE WHEN M1>0.90 THEN M1 ELSE NULL END) AS [M1>90],
COUNT(CASE WHEN M1>0.75 AND M1 < 0.90 THEN M1 ELSE NULL END) AS [M1>75],
COUNT(CASE WHEN M2>0.90 THEN M2 ELSE NULL END) AS [M2>90],
COUNT(CASE WHEN M2>0.75 AND M2 < 0.90 THEN M2 ELSE NULL END) AS [M2>75],
COUNT(CASE WHEN M3>0.90 THEN M3 ELSE NULL END) AS [M3>90],
COUNT(CASE WHEN M3>0.75 AND M3 < 0.90 THEN M2 ELSE NULL END) AS [M3>75]
FROM         dbo.X
GROUP BY Country, City
WITH CUBE
ORDER BY Country, City

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-10-06 : 23:42:14

I think in this case WITH ROLLUP would also help.

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:39:00

yep.. that can also be used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources