multiple counts in same table - 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 2000 Forums

Transact-SQL (2000)

multiple counts in same table

Author

Topic

jonneponne
Starting Member

5 Posts

Posted - 2006-11-28 : 06:46:35

I've got a table containing (among some other columns) 3 different type columns.

TABLE
xxxx as int
type 1 as string
type 2 as string
type 3 as string
date as Date

First i'd like to select the number of counts each of these appear in my table. I realized that it can be done by union 3 select statements each grouped by a type.

select type1, count(type1) from table group by type1
union
select type2, count(type2) from table group by type2...

Now this is time consuming and not what I would like to do

Secondly i'll try to do something like

SELECT type1 ,
SUM(CASE WHEN MONTH(date) = 1 THEN 1 END) AS 'Januar'
,SUM(CASE WHEN MONTH(date) = 2 THEN 1 END) AS 'Februar'
,SUM(CASE WHEN MONTH(date) = 3 THEN 1 END) AS 'Mars'
,SUM(CASE WHEN MONTH(date) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(date) = 5 THEN 1 END) AS 'Mai'
,SUM(CASE WHEN MONTH(date) = 6 THEN 1 END) AS 'Juni'
,SUM(CASE WHEN MONTH(date) = 7 THEN 1 END) AS 'Juli'
,SUM(CASE WHEN MONTH(date) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(date) = 9 THEN 1 END) AS 'Sept'
,SUM(CASE WHEN MONTH(date) = 10 THEN 1 END) AS 'Okt'
,SUM(CASE WHEN MONTH(date) = 11 THEN 1 END) AS 'Nove'
,SUM(CASE WHEN MONTH(date) = 12 THEN 1 END) AS 'Desem'
,SUM(CASE WHEN YEAR(date) = 2006 THEN 1 END) AS 'TOTAL'
FROM table
WHERE YEAR(date) = 2006
GROUP BY type

This in combination with union the other 2 types results in what I'd like to do but then again, phuu this is some crappy approach :)

Any suggustions in how to solve this?
I.E I'd like to list each type count for each month like this

Jan Feb Mars ...
type1 23 43 45
type2 12 11 15
type3 54 55 65

Any hints?

/J

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 07:05:01

Yes. Try this

SELECT		type AS 'Category',
		SUM(CASE WHEN [month] = 1 THEN 1 ELSE 0 END) AS 'Januar',
		SUM(CASE WHEN [month] = 2 THEN 1 ELSE 0 END) AS 'Februar',
		SUM(CASE WHEN [month] = 3 THEN 1 ELSE 0 END) AS 'Mars',
		SUM(CASE WHEN [month] = 4 THEN 1 ELSE 0 END) AS 'April',
		SUM(CASE WHEN [month] = 5 THEN 1 ELSE 0 END) AS 'Mai',
		SUM(CASE WHEN [month] = 6 THEN 1 ELSE 0 END) AS 'Juni',
		SUM(CASE WHEN [month] = 7 THEN 1 ELSE 0 END) AS 'Juli',
		SUM(CASE WHEN [month] = 8 THEN 1 ELSE 0 END) AS 'August',
		SUM(CASE WHEN [month] = 9 THEN 1 ELSE 0 END) AS 'Sept',
		SUM(CASE WHEN [month] = 10 THEN 1 ELSE 0 END) AS 'Okt',
		SUM(CASE WHEN [month] = 11 THEN 1 ELSE 0 END) AS 'Nove',
		SUM(CASE WHEN [month] = 12 THEN 1 ELSE 0 END) AS 'Desem',
		SUM(1) AS 'TOTAL'
FROM		(
			SELECT	[Type 1] type,
				MONTH([date]) [month]
			FROM	YourTable
			WHERE	[date] >= '20060101'
				AND [date] < '20070101'

			UNION ALL

			SELECT	[Type 2],
				MONTH([date])
			FROM	YourTable
			WHERE	[date] >= '20060101'
				AND [date] < '20070101'

			UNION ALL

			SELECT	[Type 3],
				MONTH([date])
			FROM	YourTable
			WHERE	[date] >= '20060101'
				AND [date] < '20070101'
		) m
GROUP BY	type
ORDER BY	type

Peter Larsson
Helsingborg, Sweden

jonneponne
Starting Member

5 Posts

Posted - 2006-11-28 : 07:37:13

Thanks it works, and its much less code!
But, its not faster. Is there a way to work around the unions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 08:00:47

No. Not with the table design you use today.
But it will be a lot faster if you can add a proper index for YourTable table.
Make an index for [Type 1], [Type 2], [Type 3] and [date] columns.

Then run query twice and take time second time.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-28 : 08:47:39

>>'Okt', 'Nove','Desem',

Which language is this?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 08:53:31

I believe that is portuguese or castellan.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources