Creating crosstab query using COUNT gives incorrect row and column totals

Can someone tell me why the row and column totals compute properly with this crosstab query (using SUM):
MySQL Version: 4.0.20-standard

#crosstab
SELECT MONTHNAME(dt),
      SUM(IF(category = 'A',id, 0)) AS 'cat a',
      SUM(IF(category = 'B',id, 0)) AS 'cat b',
      SUM(IF(category = 'C',id, 0)) AS 'cat c',
#row totals
      SUM(id) AS 'TOTAL'
FROM t1
GROUP BY MONTH(dt)
UNION
#column totals
SELECT 'YTD',
      SUM(IF(category = 'A',id, 0)) AS 'cat a',
      SUM(IF(category = 'B',id, 0)) AS 'cat b',
      SUM(IF(category = 'C',id, 0)) AS 'cat c',
      SUM(id) AS 'TOTAL'
FROM t1;

But, this same crosstab query using COUNT, gives incorrect Totals:

#crosstab
SELECT MONTHNAME(dt),
      COUNT(DISTINCT(IF(category = 'A',id, 0))) AS 'cat a',
      COUNT(DISTINCT(IF(category = 'B',id, 0))) AS 'cat b',
      COUNT(DISTINCT(IF(category = 'C',id, 0))) AS 'cat c',
#row totals
      COUNT(DISTINCT(id)) AS 'TOTAL'
FROM t1
GROUP BY MONTH(dt)
UNION
#column totals
SELECT 'YTD',
      COUNT(DISTINCT(IF(category = 'A',id, 0))) AS 'cat a',
      COUNT(DISTINCT(IF(category = 'B',id, 0))) AS 'cat b',
      COUNT(DISTINCT(IF(category = 'C',id, 0))) AS 'cat c',
      COUNT(DISTINCT(id)) AS 'TOTAL'
FROM t1;

-------------------------------------------------
Here's the table structure:
CREATE TABLE `t1` (                                                                                                                                      
         `id` int(11) NOT NULL default '0',                                                                                                                      
         `category` char(2) default NULL,                                                                                                                        
         `dt` date default NULL,                                                                                                                                
         PRIMARY KEY  (`id`)                                                                                                                                    
---------------------------------------------------
Here's the data:
1,A,2004-01-01,
2,B,2004-04-01,
3,C,2004-02-01,
4,A,2004-05-01,
5,B,2004-03-01,
6,C,2004-04-01,
7,A,2004-06-01,
8,B,2004-01-01,

--------------------------------------------------
I haven't been able to find anything on this topic via internet searches.
dplinnaneAsked:
Who is Participating?
 
AdrianSRUConnect With a Mentor Commented:
COUNT gives the number of non-null values.

COUNT(DISTINCT(IF(category = 'A',id, 0)))
will not be null unless id is null.

Try this:
COUNT(DISTINCT(IF(category = 'A',id, NULL)))


-Adrian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.