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
Maybe something like?
set nocount on
-- --
-- I am confused by the way that the 'type' column is laid
-- out. The table design seems to indicate that there are
-- separate columns for types 1, 2 and 3; however, the report
-- layout indicates that types 1, 2 and 3 are rather specific
-- instances of a single type column. Since the latter is
-- more commonly practiced, that is the way that I shall
-- approach the problem.
--
-- --
declare @.table table
( rid integer not null
primary key,
type varchar (10),
date datetime
)
-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number,
'Type' + convert (char(1), number%3 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table
select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (
, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (
, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from ( select type,
month (date) as [month],
count(*) as typeCount
from @.table
group by type,
month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv
-- Sample Output:
-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 11 9 10 10 11 10 10 10 5 0 0 0
-- Type2 10 10 10 10 10 10 11 10 4 0 0 0
-- Type3 10 9 11 10 10 10 10 11 4 0 0 0
|||
Unfortunatly the types are 3 different columns (at design time this report was not considered).
To work around this I union these to one "alltypes" column. This is timeconsuming and I would prefere not to do so.
Part from that your approach works find (part from pivot, I'm on SQL server 2000, not a problem though).
Is there a workaround for the unions?
Thanks
|||Sure there is; please stand by|||
set nocount on
declare @.table table
( rid integer not null
primary key,
[type 1] varchar (10),
[type 2] varchar (10),
[type 3] varchar (10),
date datetime
)
-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number + 1,
'Type' + convert (char(1), number%7 + 1),
'Type' + convert (char(1), (number+1)%7 + 1),
'Type' + convert (char(1), (number+5)%7 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table
select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from( select value as Type,
month (date) as [Month],
count(*) as typeCount
from @.table
unpivot ( value for Type in ([Type 1],[Type 2],[Type 3])
) as unpiv
group by value, month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv
-- Sample Output:
-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 14 12 12 14 13 12 14 13 6 0 0 0
-- Type2 14 12 13 13 14 12 14 13 6 0 0 0
-- Type3 14 12 13 12 14 13 13 14 5 0 0 0
-- Type4 13 12 14 12 14 13 12 14 6 0 0 0
-- Type5 12 12 14 13 13 13 13 14 5 0 0 0
-- Type6 13 12 14 13 12 14 13 13 5 0 0 0
-- Type7 13 12 13 13 13 13 14 12 6 0 0 0
