dplinnane
asked on
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.
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
COUNT(DISTINCT(IF(category
COUNT(DISTINCT(IF(category
#row totals
COUNT(DISTINCT(id)) AS 'TOTAL'
FROM t1
GROUP BY MONTH(dt)
UNION
#column totals
SELECT 'YTD',
COUNT(DISTINCT(IF(category
COUNT(DISTINCT(IF(category
COUNT(DISTINCT(IF(category
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.