Multiple counts in table

Question

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

Answer 1

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

Answer 2

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

Answer 3

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