Results 1 to 8 of 8

Thread: Count in query

  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: Count in query

    hi there, hope in your help.

    I need this output with my query:
    Code:
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Pr   | Q  | A  | B  | C  | D  | E  | A1 | A2 | A3 | A4 | A5 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Att  | 20 |  9 |  5 | 10 |  7 | 10 |  0 |  0 |  1 |  4 |  4 |
    | Lav  |  4 |  3 |  2 |  3 |  3 |  3 |  0 |  0 |  1 |  0 |  2 |
    | Lavc |  2 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |
    | Pred | 26 | 11 |  8 | 11 |  9 | 11 |  0 |  0 |  0 |  3 |  8 |
    | RGdM |  9 |  3 |  0 |  4 |  4 |  4 |  0 |  0 |  1 |  0 |  2 |
    | Tot  | 61 | 26 | 15 | 28 | 23 | 28 |  0 |  0 |  3 |  7 | 16 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    But the output in column A1, A2, A3, A4 and A5 is wrong, can you help me?
    I add my dotable.
    thank you.
    Code:
    mysql> SELECT
    	COALESCE (Att, 'Tot') AS Pr,
    	Q,
    	A,
    	B,
    	C,
    	D,
    	E,
    	A1,
    	A2,
    	A3,
    	A4,
    	A5
    FROM
    	(
    		SELECT
    			ATT,
    			COUNT(*) AS Q,
    			COUNT(A) AS A,
    			COUNT(B) AS B,
    			COUNT(C) AS C,
    			COUNT(D) AS D,
    			COUNT(E) AS E,
    
    		IF (A = 1, 1, 0) AS A1,
    
    	IF (A = 2, 1, 0) AS A2,
    
    IF (A = 3, 1, 0) AS A3,
    
    IF (A = 4, 1, 0) AS A4,
    
    IF (A = 5, 1, 0) AS A5
    FROM
    	`dotable`
    GROUP BY
    	Att WITH ROLLUP
    	) qs;
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Pr   | Q  | A  | B  | C  | D  | E  | A1 | A2 | A3 | A4 | A5 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Att  | 20 |  9 |  5 | 10 |  7 | 10 |  0 |  0 |  0 |  0 |  0 |
    | Lav  |  4 |  3 |  2 |  3 |  3 |  3 |  0 |  0 |  0 |  0 |  1 |
    | Lavc |  2 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |
    | Pred | 26 | 11 |  8 | 11 |  9 | 11 |  0 |  0 |  0 |  1 |  0 |
    | RGdM |  9 |  3 |  0 |  4 |  4 |  4 |  0 |  0 |  0 |  0 |  0 |
    | Tot  | 61 | 26 | 15 | 28 | 23 | 28 |  0 |  0 |  0 |  0 |  0 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    6 rows in set
    
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `doTable`
    -- ----------------------------
    DROP TABLE IF EXISTS `doTable`;
    CREATE TABLE `doTable` (
      `Att` varchar(255) DEFAULT NULL,
      `A` int(10) DEFAULT NULL,
      `B` int(10) DEFAULT NULL,
      `C` int(10) DEFAULT NULL,
      `D` int(10) DEFAULT NULL,
      `E` int(10) DEFAULT NULL,
      `F` int(10) DEFAULT NULL,
      `G` int(10) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=62 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of doTable
    -- ----------------------------
    INSERT INTO `doTable` VALUES ('RGdM', null, null, null, null, null, null, null, '1');
    INSERT INTO `doTable` VALUES ('RGdM', '5', null, '5', '5', '5', null, null, '2');
    INSERT INTO `doTable` VALUES ('RGdM', '3', null, '3', '3', '3', null, null, '3');
    INSERT INTO `doTable` VALUES ('RGdM', '5', null, '5', '5', '4', null, null, '4');
    INSERT INTO `doTable` VALUES ('RGdM', null, null, null, null, null, null, null, '5');
    INSERT INTO `doTable` VALUES ('Lav', '5', '5', '3', '5', '5', null, '1', '6');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '7');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, '1', null, '8');
    INSERT INTO `doTable` VALUES ('Lav', '3', null, '4', '4', '3', null, '2', '9');
    INSERT INTO `doTable` VALUES ('Pred', '4', null, '4', '5', '5', null, null, '10');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '11');
    INSERT INTO `doTable` VALUES ('Pred', '4', '2', '3', '4', '4', null, '2', '12');
    INSERT INTO `doTable` VALUES ('Lav', null, null, null, null, null, null, null, '13');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, '1', null, '14');
    INSERT INTO `doTable` VALUES ('Lav', '5', '5', '5', '5', '5', null, null, '15');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '16');
    INSERT INTO `doTable` VALUES ('Att', '4', null, '4', '4', '4', null, null, '17');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '18');
    INSERT INTO `doTable` VALUES ('Att', '5', '5', '5', '5', '5', null, null, '19');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '20');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, '1', '4', '21');
    INSERT INTO `doTable` VALUES ('Att', '5', '5', '5', '5', '5', null, null, '22');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '23');
    INSERT INTO `doTable` VALUES ('Pred', '5', '5', '5', '5', '5', null, null, '24');
    INSERT INTO `doTable` VALUES ('Att', '4', '5', '4', '4', '4', null, null, '25');
    INSERT INTO `doTable` VALUES ('Att', '4', null, '4', '4', '4', null, null, '26');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '27');
    INSERT INTO `doTable` VALUES ('Att', '4', '4', '4', '4', '4', null, null, '28');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '29');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '30');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '31');
    INSERT INTO `doTable` VALUES ('RGdM', null, null, '4', '5', '3', '0', '5', '32');
    INSERT INTO `doTable` VALUES ('RGdM', null, null, null, null, null, null, null, '33');
    INSERT INTO `doTable` VALUES ('RGdM', null, null, null, null, null, null, null, '34');
    INSERT INTO `doTable` VALUES ('RGdM', null, null, null, null, null, null, null, '35');
    INSERT INTO `doTable` VALUES ('Att', null, null, '5', null, '5', '1', '5', '36');
    INSERT INTO `doTable` VALUES ('Pred', '5', '5', '5', '5', '5', null, null, '37');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '38');
    INSERT INTO `doTable` VALUES ('Pred', '5', null, '5', null, '4', '1', null, '39');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '40');
    INSERT INTO `doTable` VALUES ('Att', '3', null, '5', null, '4', '1', null, '41');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, '1', null, '42');
    INSERT INTO `doTable` VALUES ('Pred', '5', '5', '5', '5', '5', null, null, '43');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '44');
    INSERT INTO `doTable` VALUES ('Att', '5', null, '5', null, '4', '0', '2', '45');
    INSERT INTO `doTable` VALUES ('Lavc', null, null, null, null, null, null, null, '46');
    INSERT INTO `doTable` VALUES ('Att', null, null, null, null, null, null, null, '47');
    INSERT INTO `doTable` VALUES ('Pred', '5', '5', '5', '5', '5', null, null, '48');
    INSERT INTO `doTable` VALUES ('Lavc', null, null, null, null, null, null, null, '49');
    INSERT INTO `doTable` VALUES ('Pred', '5', '5', '5', '5', '5', null, null, '50');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '51');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '52');
    INSERT INTO `doTable` VALUES ('Pred', '5', '5', '5', '4', '5', null, null, '53');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '54');
    INSERT INTO `doTable` VALUES ('Pred', '4', null, '5', null, '5', null, null, '55');
    INSERT INTO `doTable` VALUES ('Pred', '5', '4', '4', '5', '4', '0', null, '56');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '57');
    INSERT INTO `doTable` VALUES ('Att', '5', '5', '5', '5', '5', null, null, '58');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '59');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '60');
    INSERT INTO `doTable` VALUES ('Pred', null, null, null, null, null, null, null, '61');

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why no COUNT function nor SUM function was used for A1, A2, A3, A4 and A5?

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by tonkuma View Post
    Why no COUNT function nor SUM function was used for A1, A2, A3, A4 and A5?
    I used your observation and got this:

    Code:
    Pr	Q	A	B	C	D	E	A1	A2	A3	A4	A5
    Att	20	9	5	10	7	10	0	0	1	4	4
    Lav	4	3	2	3	3	3	0	0	1	0	2
    Lavc	2	0	0	0	0	0	0	0	0	0	0
    Pred	26	11	8	11	9	11	0	0	0	3	8
    RGdM	9	3	0	4	4	4	0	0	1	0	2
    Tot	61	26	15	28	23	28	0	0	3	7	16
    Here is the modified query:

    Code:
    SELECT
    	COALESCE (Att, 'Tot') AS Pr,
    	Q,
    	A,
    	B,
    	C,
    	D,
    	E,
    	A1,
    	A2,
    	A3,
    	A4,
    	A5
    FROM
    	(
    		SELECT
    			ATT,
    			COUNT(*) AS Q,
    			COUNT(A) AS A,
    			COUNT(B) AS B,
    			COUNT(C) AS C,
    			COUNT(D) AS D,
    			COUNT(E) AS E,
    
    		SUM(CASE WHEN A = 1 THEN 1 ELSE 0 END) AS A1,
    		SUM(CASE WHEN A = 2 THEN 1 ELSE 0 END) AS A2,
    		SUM(CASE WHEN A = 3 THEN 1 ELSE 0 END) AS A3,
    		SUM(CASE WHEN A = 4 THEN 1 ELSE 0 END) AS A4,
                    SUM(CASE WHEN A = 5 THEN 1 ELSE 0 END) AS A5
     FROM doTable
    GROUP BY
    	Att WITH ROLLUP
    	) qs
    Seems to work! I'm not sure the original query could even run without an error.

    Looking at the query again, I could not really see why there was a need for a sub-select.
    So I tried just running this:
    Code:
    		SELECT
    			Coalesce (ATT, 'Tot') as Pr,
    			COUNT(*) AS Q,
    			COUNT(A) AS A,
    			COUNT(B) AS B,
    			COUNT(C) AS C,
    			COUNT(D) AS D,
    			COUNT(E) AS E,
    
    		SUM(CASE WHEN A = 1 THEN 1 ELSE 0 END) AS A1,
    		SUM(CASE WHEN A = 2 THEN 1 ELSE 0 END) AS A2,
    		SUM(CASE WHEN A = 3 THEN 1 ELSE 0 END) AS A3,
    		SUM(CASE WHEN A = 4 THEN 1 ELSE 0 END) AS A4,
                    SUM(CASE WHEN A = 5 THEN 1 ELSE 0 END) AS A5
     FROM doTable
    GROUP BY Att WITH ROLLUP
    I got the same results. Unless there is a bigger picture that I am not aware of. . .
    Last edited by LinksUp; 04-13-13 at 04:44.

  4. #4
    Join Date
    Aug 2012
    Posts
    30
    Good Day All.
    Thanks a bunch for your response.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that LinksUp's last query(without sub-select) seems rational and correct.


    [The following paragraph was based on manuals. Not tested.]
    By the way,

    SUM(CASE WHEN A = x THEN 1 ELSE 0 END) AS Ax
    Where x = 1, 2, 3, 4 or 5

    may be replaced by

    COUNT(CASE WHEN A = x THEN 0 END) AS Ax
    Where x = 1, 2, 3, 4 or 5

    Note1: The value "0" in "THEN 0" may be replaced by any valid non-null value.
    I used 0, because it is the unit of addition,
    on the other hand, 1 is the unit of multiplication.

    Note2: Both of SUM or COUNT may return same results.
    But, SUM requires ELSE 0. If without that, null value would be returned instead of 0.

  6. #6
    Join Date
    Aug 2012
    Posts
    30
    Quote Originally Posted by tonkuma View Post
    I think that LinksUp's last query(without sub-select) seems rational and correct.


    [The following paragraph was based on manuals. Not tested.]
    By the way,

    SUM(CASE WHEN A = x THEN 1 ELSE 0 END) AS Ax
    Where x = 1, 2, 3, 4 or 5

    may be replaced by

    COUNT(CASE WHEN A = x THEN 0 END) AS Ax
    Where x = 1, 2, 3, 4 or 5

    Note1: The value "0" in "THEN 0" may be replaced by any valid non-null value.
    I used 0, because it is the unit of addition,
    on the other hand, 1 is the unit of multiplication.

    Note2: Both of SUM or COUNT may return same results.
    But, SUM requires ELSE 0. If without that, null value would be returned instead of 0.
    Thanks your version have correct output:
    Code:
    mysql> SELECT
    	COALESCE (ATT, 'Tot') AS Pr,
    	COUNT(*) AS Q,
    	COUNT(A) AS A,
    	COUNT(B) AS B,
    	COUNT(C) AS C,
    	COUNT(D) AS D,
    	COUNT(E) AS E,
    	COUNT(CASE WHEN A = 1 THEN 0 END) AS A1,
    	COUNT(CASE WHEN A = 2 THEN 0 END) AS A2,
    	COUNT(CASE WHEN A = 3 THEN 0 END) AS A3,
    	COUNT(CASE WHEN A = 4 THEN 0 END) AS A4,
    	COUNT(CASE WHEN A = 5 THEN 0 END) AS A5
    FROM
    	doTable
    GROUP BY
    	Att WITH ROLLUP;
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Pr   | Q  | A  | B  | C  | D  | E  | A1 | A2 | A3 | A4 | A5 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Att  | 20 |  9 |  5 | 10 |  7 | 10 |  0 |  0 |  1 |  4 |  4 |
    | Lav  |  4 |  3 |  2 |  3 |  3 |  3 |  0 |  0 |  1 |  0 |  2 |
    | Lavc |  2 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |
    | Pred | 26 | 11 |  8 | 11 |  9 | 11 |  0 |  0 |  0 |  3 |  8 |
    | RGdM |  9 |  3 |  0 |  4 |  4 |  4 |  0 |  0 |  1 |  0 |  2 |
    | Tot  | 61 | 26 | 15 | 28 | 23 | 28 |  0 |  0 |  3 |  7 | 16 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    6 rows in set
    Last edited by cms9651; 04-13-13 at 08:10.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wrote
    COUNT(CASE WHEN A = x THEN 0 END) AS Ax
    Where x = 1, 2, 3, 4 or 5

    Note1: The value "0" in "THEN 0" may be replaced by any valid non-null value.
    I used 0, because it is the unit of addition,
    on the other hand, 1 is the unit of multiplication.
    "THEN 0" was used only in "COUNT(...)" in my last post.
    "SUM(...)" in my last post used "THEN 1".

    So, Note1 is not applicable to SUM.

    But, you used
    SUM(CASE WHEN A = 1 THEN 0 END) AS A1,
    so on...

    Please try by
    COUNT(CASE WHEN A = 1 THEN 0 END) AS A1,
    so on...
    Last edited by tonkuma; 04-13-13 at 08:19.

  8. #8
    Join Date
    Aug 2012
    Posts
    30
    Thanks your version have correct output:
    Code:
    mysql> SELECT
    	COALESCE (ATT, 'Tot') AS Pr,
    	COUNT(*) AS Q,
    	COUNT(A) AS A,
    	COUNT(B) AS B,
    	COUNT(C) AS C,
    	COUNT(D) AS D,
    	COUNT(E) AS E,
    	COUNT(CASE WHEN A = 1 THEN 0 END) AS A1,
    	COUNT(CASE WHEN A = 2 THEN 0 END) AS A2,
    	COUNT(CASE WHEN A = 3 THEN 0 END) AS A3,
    	COUNT(CASE WHEN A = 4 THEN 0 END) AS A4,
    	COUNT(CASE WHEN A = 5 THEN 0 END) AS A5
    FROM
    	doTable
    GROUP BY
    	Att WITH ROLLUP;
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Pr   | Q  | A  | B  | C  | D  | E  | A1 | A2 | A3 | A4 | A5 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    | Att  | 20 |  9 |  5 | 10 |  7 | 10 |  0 |  0 |  1 |  4 |  4 |
    | Lav  |  4 |  3 |  2 |  3 |  3 |  3 |  0 |  0 |  1 |  0 |  2 |
    | Lavc |  2 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |
    | Pred | 26 | 11 |  8 | 11 |  9 | 11 |  0 |  0 |  0 |  3 |  8 |
    | RGdM |  9 |  3 |  0 |  4 |  4 |  4 |  0 |  0 |  1 |  0 |  2 |
    | Tot  | 61 | 26 | 15 | 28 | 23 | 28 |  0 |  0 |  3 |  7 | 16 |
    +------+----+----+----+----+----+----+----+----+----+----+----+
    6 rows in set

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •