Thread: Count in query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0

    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
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    this looks extremely familiar

    why did you start a new user id?

    are you posting this on sitepoint as well?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo