Counting Rows - 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 2008 Forums

Transact-SQL (2008)

Counting Rows

Author

Topic

SalemPoe
Starting Member

1 Post

Posted - 2014-04-24 : 14:26:22

Hello Everyone,

I am trying to construct a query that will allow me to return a count on multiple items. Here is where I have started:

; WITH CTE AS (SELECT Juris, dataident, entrydate,

CASE Hazard Class WHEN Class 1 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 2 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 3 THEN '' ELSE Hazard Class END AS Hazard Class
CASE Hazard Class WHEN Class 4 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 5 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 6 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 7 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 8 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Class 9 THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Sewage THEN '' ELSE Hazard Class END AS Hazard Class,
CASE Hazard Class WHEN Suspicious Powder/Package THEN '' ELSE Hazard Class END AS Hazard Class,
ROW_NUMBER() OVER (PARTITION BY dpdnJurisdiction ORDER BY entrydate DESC) AS RN
FROM dbo.table_655
WHERE global IS NOT NULL
SELECT CAST(count(Hazard Class)AS varchar(100)) AS [Hazard Class] FROM CTE WHERE RN=1 AND (Hazard Class = 'Class 1’
OR Hazard Class = 'Class 2’
OR Hazard Class = 'Class 3’
OR Hazard Class = 'Class 4’
OR Hazard Class = 'Class 5’
OR Hazard Class = 'Class 6’
OR Hazard Class = 'Class 7’
OR Hazard Class = 'Class 8’
OR Hazard Class = 'Class 9’
OR Hazard Class = 'Sewage'
OR Hazard Class = 'Suspicious Powder/Package')

I am looking for a return data set something like this:

Class 1 17
Class 2 5
Class 3 52
Class 4 2
Class 5 16
etc. etc.

Have I missed the boat? Any assistance would be greatly appreciated.

THANK YOU IN ADVANCE!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-24 : 17:03:53

The syntax has several issues - for example, this construct is not correct:

CASE Hazard Class WHEN Class 1 THEN '' ELSE Hazard Class END AS Hazard Class,

If you are trying to get a count of the different classes, can you get it with a simple count and group by?

SELECT Class AS [HazardClass], COUNT(*)
FROM dbo.table_655
GROUP BY  Class;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources