Solved

Creating crosstab query using COUNT gives incorrect row and column totals

Posted on 2004-09-15
1
433 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:dplinnane
1 Comment
 
LVL 12

Accepted Solution

by:
AdrianSRU earned 500 total points
ID: 12070426
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question