Count Zero Scores - 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)

Count Zero Scores

Author

Topic

mystifier
Starting Member

12 Posts

Posted - 2012-05-14 : 15:20:51

Sites returns forms with 5 kpi_points which are scored 1,2,3,4 or 5
I need to count frequency of scores.

Example Data:
'Site01',2,3,3,4,3
'Site01',3,4,3,2,4
'Site01',3,4,3,2,3

My query is:

SELECT Score, Siteref AS Freq FROM siteforms
UNPIVOT (Score For siteforms In (kpi1,kpi2,kpi3,kpi4,kpi5)) AS U
WHERE (Siteref = 'Site01')
GROUP BY Score ORDER BY Score

Sample data would return:
2,3
3,8
4,4

I need it to return:
1,0
2,3
3,8
4,4
5,0

To include 1,2,3,4 and 5 even where count is zero.

I have tried:

case when count(Siteref)>0 then COUNT(siteref) else 0 end

and:

isnull(count(Siteref),0)

but these do not have any effect.

Can anyone help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-14 : 16:44:32

[code]DECLARE @Sample TABLE
(
SiteRef VARCHAR(10) NOT NULL,
Kpi1 INT,
Kpi2 INT,
Kpi3 INT,
Kpi4 INT,
Kpi5 INT
)

INSERT @Sample
VALUES ('Site01', 2, 3, 3, 4, 3),
('Site01', 3, 4, 3, 2, 4),
('Site01', 3, 4, 3, 2, 3)

-- SwePeso
SELECT f.Score,
SUM(f.Items) AS Items
FROM @Sample AS s
CROSS APPLY (
VALUES (s.Kpi1, 1),
(s.Kpi2, 1),
(s.Kpi3, 1),
(s.Kpi4, 1),
(s.Kpi5, 1),
(0, 0),
(1, 0),
(2, 0),
(3, 0),
(4, 0),
(5, 0)
) AS f(Score, Items)
WHERE s.SiteRef = 'Site01'
GROUP BY f.Score
ORDER BY f.Score[/code]

N 56°04'39.26"
E 12°55'05.63"

mystifier
Starting Member

12 Posts

Posted - 2012-05-14 : 17:12:20

Perfect SwePeso,

Thanks a lot - some new territory for me there

mystifier
Starting Member

12 Posts

Posted - 2012-05-15 : 05:15:05

Frustratingly, having difficulty adding a column to add percentage distribution:

1,0,0
2,3,20
3,8,53.33
4,4,26.67
5,0,0

eg. 8 scores of 3 accounting for 53.33 of results.

It seems like a very useful construct when I can get my head round it!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-15 : 06:36:09

This?

DECLARE	@Sample TABLE
	(
		SiteRef VARCHAR(10) NOT NULL,
		Kpi1 INT,
		Kpi2 INT,
		Kpi3 INT,
		Kpi4 INT,
		Kpi5 INT
	)

INSERT	@Sample
VALUES	('Site01', 2, 3, 3, 4, 3),
	('Site01', 3, 4, 3, 2, 4),
	('Site01', 3, 4, 3, 2, 3)


-- SwePeso
;WITH cteSource(Score, Items)
AS (
	SELECT		f.Score,
			SUM(f.Items) AS Items
	FROM		@Sample AS s
	CROSS APPLY	(
				VALUES	(s.Kpi1, 1),
					(s.Kpi2, 1),
					(s.Kpi3, 1),
					(s.Kpi4, 1),
					(s.Kpi5, 1),
					(0, 0),
					(1, 0),
					(2, 0),
					(3, 0),
					(4, 0),
					(5, 0)
			) AS f(Score, Items)
	WHERE		s.SiteRef = 'Site01'
	GROUP BY	f.Score
)
SELECT		Score,
		Items,
		100E * Items / SUM(Items) OVER () AS Percentage
FROM		cteSource
ORDER BY	Score

N 56°04'39.26"
E 12°55'05.63"

mystifier
Starting Member

12 Posts

Posted - 2012-05-15 : 07:18:32

On the one hand, thank-you for a concise query that gives exactly what I want; on the other 'thank-you' for making me realise how little I know.

Seriously, thanks SwePeso - Great lesson!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources