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

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0

    Clause with rollup


    Hi there, hope in your help.

    I tried this query but the output in the last row of column Qst_prest is incorrect because I don't have the sum and total of all rows, but I've the value of row strD is equal to DDD .

    I need this output:
    Code:
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    | Qst    | Strd   | Dimension    | E1 | E2 | E3 | E4 | E5 | E6 | SumsE | new_output | Qst_prest |
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    |      3 | AAA    | Lav          | 1  | 0  | 0  | 0  | 2  | 0  | 3     | 3.7        |       787 |
    |      8 | BBB    | Lav          | 0  | 0  | 1  | 3  | 4  | 0  | 8     | 4.4        |       887 |
    |      5 | CCC    | Lav          | 0  | 0  | 1  | 0  | 4  | 0  | 5     | 4.6        |       976 |
    |      5 | DDD    | Lav          | 0  | 0  | 0  | 0  | 4  | 0  | 4     | 5.0        |       338 |
    |     21 | Tot    | Lav          | 1  | 0  | 2  | 3  | 14 | 0  | 20    | 4.5        |      2988 |
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    Can you help me?

    Any suggestion will be appreciated
    Code:
    SELECT
    	Qst,
    	Strd,
    	IFNULL(activated, 'Tot') AS Dimension,
    	sums.E1,
    	sums.E2,
    	sums.E3,
    	sums.E4,
    	sums.E5,
    	sums.E6,
    	sums.E1 + sums.E2 + sums.E3 + sums.E4 + sums.E5 + sums.E6 AS SumsE,
    	ROUND(
    		(
    			(sums.E1 * 1) + (sums.E2 * 2) + (sums.E3 * 3) + (sums.E4 * 4) + (sums.E5 * 5) + (sums.E6 * 6)
    		) / (
    			sums.E1 + sums.E2 + sums.E3 + sums.E4 + sums.E5 + sums.E6
    		),
    		1
    	) AS new_output,
    	Qst_prest
    FROM
    	(
    		SELECT
    			COUNT(*) AS Qst,
    			Strd,
    			activated,
    			SUM(IF(E = 1, 1, 0)) AS 'E1',
    			SUM(IF(E = 2, 1, 0)) AS 'E2',
    			SUM(IF(E = 3, 1, 0)) AS 'E3',
    			SUM(IF(E = 4, 1, 0)) AS 'E4',
    			SUM(IF(E = 5, 1, 0)) AS 'E5',
    			SUM(IF(E = 6, 1, 0)) AS 'E6',
    			B.Qst AS Qst_prest
    		FROM
    			tbl_a A
    		JOIN tbl_b B ON A.activated = B.prest
    		AND A.Strd = B.Strds
    		WHERE
    			(
    				A IS NOT NULL
    				OR B IS NOT NULL
    				OR C IS NOT NULL
    				OR D IS NOT NULL
    				OR E IS NOT NULL
    			)
    		AND activated = 'Lav'
    		GROUP BY
    			Strd WITH ROLLUP
    	) AS sums;
    
    
    
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    | Qst    | Strd   | Dimension    | E1 | E2 | E3 | E4 | E5 | E6 | SumsE | new_output | Qst_prest |
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    |      3 | AAA    | Lav          | 1  | 0  | 0  | 0  | 2  | 0  | 3     | 3.7        |       787 |
    |      8 | BBB    | Lav          | 0  | 0  | 1  | 3  | 4  | 0  | 8     | 4.4        |       887 |
    |      5 | CCC    | Lav          | 0  | 0  | 1  | 0  | 4  | 0  | 5     | 4.6        |       976 |
    |      5 | DDD    | Lav          | 0  | 0  | 0  | 0  | 4  | 0  | 4     | 5.0        |       338 |
    |     21 | Tot    | Lav          | 1  | 0  | 2  | 3  | 14 | 0  | 20    | 4.5        |       338 |
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    you have three non-aggregate columns in your subquery -- Strd, activated, and B.Qst

    however, your GROUP BY clause includes only one of them

    also, it looks like you want a LEFT OUTER JOIN instead of INNER JOIN in your subquery, but it`s rather difficult to understand because you neglected to provide the table layouts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    hello r397, how are you?

    thanks so much for help, I tried your suggestion, but I've this output.

    the table layouts bottom of the post.

    Cheers.

    Code:
    mysql> SELECT
    	Qst,
    	IFNULL(Strd, 'Tot') AS Strd,
    	IFNULL(activated, 'Tot') AS Dimension,
    	sums.E1,
    	sums.E2,
    	sums.E3,
    	sums.E4,
    	sums.E5,
    	sums.E6,
    	sums.E1 + sums.E2 + sums.E3 + sums.E4 + sums.E5 + sums.E6 AS SumsE,
    	ROUND(
    		(
    			(sums.E1 * 1) + (sums.E2 * 2) + (sums.E3 * 3) + (sums.E4 * 4) + (sums.E5 * 5) + (sums.E6 * 6)
    		) / (
    			sums.E1 + sums.E2 + sums.E3 + sums.E4 + sums.E5 + sums.E6
    		),
    		1
    	) AS new_output,
    	Qst_prest
    FROM
    	(
    		SELECT
    			COUNT(*) AS Qst,
    			Strd,
    			activated,
    			SUM(IF(E = 1, 1, 0)) AS 'E1',
    			SUM(IF(E = 2, 1, 0)) AS 'E2',
    			SUM(IF(E = 3, 1, 0)) AS 'E3',
    			SUM(IF(E = 4, 1, 0)) AS 'E4',
    			SUM(IF(E = 5, 1, 0)) AS 'E5',
    			SUM(IF(E = 6, 1, 0)) AS 'E6',
    			B.Qst AS Qst_prest
    		FROM
    			tbl_A A
    		LEFT OUTER JOIN tbl_B B ON A.activated = B.prest
    		AND A.Strd = B.Strds
    		WHERE
    			E IS NOT NULL
    		AND activated = 'LavC'
    		GROUP BY
    			Strd,
    			activated,
    			qst WITH ROLLUP
    	) AS sums;
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
    | Qst | Strd | Dimension | E1 | E2 | E3 | E4 | E5 | E6 | SumsE | new_output | Qst_prest |
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
    |   2 | BBB  | LavC      | 0  | 0  | 0  | 0  | 2  | 0  | 2     | 5.0        |       134 |
    |   2 | BBB  | LavC      | 0  | 0  | 0  | 0  | 2  | 0  | 2     | 5.0        |       134 |
    |   2 | BBB  | Tot       | 0  | 0  | 0  | 0  | 2  | 0  | 2     | 5.0        |       134 |
    |   2 | CCC  | LavC      | 0  | 0  | 0  | 2  | 0  | 0  | 2     | 4.0        |        99 |
    |   2 | CCC  | LavC      | 0  | 0  | 0  | 2  | 0  | 0  | 2     | 4.0        |        99 |
    |   2 | CCC  | Tot       | 0  | 0  | 0  | 2  | 0  | 0  | 2     | 4.0        |        99 |
    |   1 | DDD  | LavC      | 0  | 0  | 0  | 0  | 1  | 0  | 1     | 5.0        |        75 |
    |   1 | DDD  | LavC      | 0  | 0  | 0  | 0  | 1  | 0  | 1     | 5.0        |        75 |
    |   1 | DDD  | Tot       | 0  | 0  | 0  | 0  | 1  | 0  | 1     | 5.0        |        75 |
    |   5 | Tot  | Tot       | 0  | 0  | 0  | 2  | 3  | 0  | 5     | 4.6        |        75 |
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
    10 rows in set
    
    
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `tbl_a`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_a`;
    CREATE TABLE `tbl_a` (
      `activated` varchar(255) DEFAULT NULL,
      `Strd` varchar(255) DEFAULT NULL,
      `E` int(10) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=160 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_a
    -- ----------------------------
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '1');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '2');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '3');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '4');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '5');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '6');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '3', '7');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '8');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '4', '9');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '4', '10');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '1', '11');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '12');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '13');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '14');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '15');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '16');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '17');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '18');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '19');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '20');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '21');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '22');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '23');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '24');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', null, '25');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '26');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '27');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', null, '28');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '29');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '30');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '3', '31');
    INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', null, '32');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', null, '33');
    INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '34');
    INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', '4', '35');
    INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', null, '36');
    INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', '4', '37');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '38');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '39');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '40');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '41');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '42');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '43');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '44');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '3', '45');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', null, '46');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', null, '47');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', null, '48');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '49');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '50');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '51');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '4', '52');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '53');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '4', '54');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '55');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '56');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '57');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '4', '58');
    INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', null, '59');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '60');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '61');
    INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', null, '62');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '63');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '64');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '65');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '66');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '67');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '68');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '4', '69');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '70');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '71');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '72');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '73');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '74');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '75');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '76');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '77');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', null, '78');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '5', '79');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '3', '80');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '4', '81');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', null, '82');
    INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '83');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '84');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '85');
    INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '3', '86');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '87');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '88');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '4', '89');
    INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', null, '90');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '91');
    INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '92');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '93');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '94');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '95');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '96');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '97');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '98');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '99');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '100');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '101');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '102');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '103');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '104');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '105');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '106');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '107');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '5', '108');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '109');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '4', '110');
    INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', '5', '111');
    INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', '5', '112');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '113');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '4', '114');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '115');
    INSERT INTO `tbl_a` VALUES ('LavS', 'BBB', '5', '116');
    INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '117');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '118');
    INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '3', '119');
    INSERT INTO `tbl_a` VALUES ('LavS', 'BBB', '1', '120');
    INSERT INTO `tbl_a` VALUES ('LavS', 'BBB', '5', '121');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '4', '122');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '123');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '124');
    INSERT INTO `tbl_a` VALUES ('PredC', 'AAA', '4', '125');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '126');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '127');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '128');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '129');
    INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '130');
    INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '131');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '4', '132');
    INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '133');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '4', '134');
    INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '135');
    INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '4', '136');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '137');
    INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '138');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '4', '139');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'DDD', '5', '140');
    INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '5', '141');
    INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '5', '142');
    INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '1', '143');
    INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '4', '144');
    INSERT INTO `tbl_a` VALUES ('Act', 'DDD', null, '145');
    INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '146');
    INSERT INTO `tbl_a` VALUES ('PredS', 'DDD', '5', '147');
    INSERT INTO `tbl_a` VALUES ('PredS', 'DDD', '5', '148');
    INSERT INTO `tbl_a` VALUES ('PredS', 'DDD', '5', '149');
    INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '150');
    INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', null, '151');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '5', '152');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '5', '153');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'DDD', '5', '154');
    INSERT INTO `tbl_a` VALUES ('RiprG', 'DDD', '5', '155');
    INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '156');
    INSERT INTO `tbl_a` VALUES ('LavC', 'DDD', '5', '157');
    INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '158');
    INSERT INTO `tbl_a` VALUES ('LavC', 'AAA', null, '159');
    
    
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `tbl_b`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_b`;
    CREATE TABLE `tbl_b` (
      `Strds` char(3) DEFAULT NULL,
      `prest` char(50) DEFAULT NULL,
      `Qst` int(11) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_b
    -- ----------------------------
    INSERT INTO `tbl_b` VALUES ('BBB', 'Act', '493', '1');
    INSERT INTO `tbl_b` VALUES ('BBB', 'LavS', '787', '2');
    INSERT INTO `tbl_b` VALUES ('BBB', 'LavC', '134', '3');
    INSERT INTO `tbl_b` VALUES ('BBB', 'RiprG', '252', '4');
    INSERT INTO `tbl_b` VALUES ('CCC', 'Act', '532', '5');
    INSERT INTO `tbl_b` VALUES ('CCC', 'LavS', '887', '6');
    INSERT INTO `tbl_b` VALUES ('CCC', 'LavC', '99', '7');
    INSERT INTO `tbl_b` VALUES ('CCC', 'RiprG', '457', '8');
    INSERT INTO `tbl_b` VALUES ('AAA', 'Act', '729', '9');
    INSERT INTO `tbl_b` VALUES ('AAA', 'LavS', '976', '10');
    INSERT INTO `tbl_b` VALUES ('AAA', 'LavC', '110', '11');
    INSERT INTO `tbl_b` VALUES ('AAA', 'RiprG', '312', '12');
    INSERT INTO `tbl_b` VALUES ('DDD', 'Act', '206', '13');
    INSERT INTO `tbl_b` VALUES ('DDD', 'LavS', '338', '14');
    INSERT INTO `tbl_b` VALUES ('DDD', 'LavC', '75', '15');
    INSERT INTO `tbl_b` VALUES ('DDD', 'RiprG', '151', '16');
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by cms9651-2
    I tried your suggestion, but I've this output.
    so what's wrong with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    so what's wrong with it?
    I need this output:
    PHP Code:
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
    Qst Strd Dimension E1 E2 E3 E4 E5 E6 SumsE new_output Qst_prest |
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
    |   
    BBB  LavC      0  0  0  0  2  0  2     5.0        |       134 |
    |   
    CCC  LavC      0  0  0  2  0  0  2     4.0        |        99 |
    |   
    DDD  LavC      0  0  0  0  1  0  1     5.0        |        75 |
    |   
    Tot  Tot       0  0  0  2  3  0  5     4.6        |       308 |
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+ 
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    try fixing the subquery's use of "qst" for two different things
    Code:
    SELECT
       COUNT(*) AS Qst,
       Strd,
       activated,
       SUM(IF(E = 1, 1, 0)) AS 'E1',
       SUM(IF(E = 2, 1, 0)) AS 'E2',
       SUM(IF(E = 3, 1, 0)) AS 'E3',
       SUM(IF(E = 4, 1, 0)) AS 'E4',
       SUM(IF(E = 5, 1, 0)) AS 'E5',
       SUM(IF(E = 6, 1, 0)) AS 'E6',
       B.Qst  AS Qst_prest
      FROM
       tbl_A A
      LEFT OUTER JOIN tbl_B B ON A.activated = B.prest
      AND A.Strd = B.Strds
      WHERE
       E IS NOT NULL
      AND activated = 'LavC'
      GROUP BY
       Strd,
       activated,
       qst  WITH ROLLUP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    try fixing the subquery's use of "qst" for two different things


    PHP Code:
    mysqlSELECT
       COUNT
    (*) AS Qst,
       
    Strd,
       
    activated,
       
    SUM(IF(110)) AS 'E1',
       
    SUM(IF(210)) AS 'E2',
       
    SUM(IF(310)) AS 'E3',
       
    SUM(IF(410)) AS 'E4',
       
    SUM(IF(510)) AS 'E5',
       
    SUM(IF(610)) AS 'E6',
       
    B.Qst  AS Qst_prest
      FROM
       tbl_A A
      LEFT OUTER JOIN tbl_B B ON A
    .activated B.prest
      
    AND A.Strd B.Strds
      WHERE
       E IS NOT NULL
      
    AND activated 'LavC'
      
    GROUP BY
       Strd
    ,
       
    activated,
       
    qst  WITH ROLLUP;
    +-----+------+-----------+----+----+----+----+----+----+-----------+
    Qst Strd activated E1 E2 E3 E4 E5 E6 Qst_prest |
    +-----+------+-----------+----+----+----+----+----+----+-----------+
    |   
    BBB  LavC      0  0  0  0  2  0  |       134 |
    |   
    BBB  LavC      0  0  0  0  2  0  |       134 |
    |   
    BBB  NULL      0  0  0  0  2  0  |       134 |
    |   
    CCC  LavC      0  0  0  2  0  0  |        99 |
    |   
    CCC  LavC      0  0  0  2  0  0  |        99 |
    |   
    CCC  NULL      0  0  0  2  0  0  |        99 |
    |   
    DDD  LavC      0  0  0  0  1  0  |        75 |
    |   
    DDD  LavC      0  0  0  0  1  0  |        75 |
    |   
    DDD  NULL      0  0  0  0  1  0  |        75 |
    |   
    NULL NULL      0  0  0  2  3  0  |        75 |
    +-----+------+-----------+----+----+----+----+----+----+-----------+
    10 rows in set 
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    you did not fix the "qst" problem like i suggested

    here, i fixed it for you


    Code:
    SELECT 
       COUNT(*) AS sonova_beesh, 
       Strd, 
       activated, 
       SUM(IF(E = 1, 1, 0)) AS 'E1', 
       SUM(IF(E = 2, 1, 0)) AS 'E2', 
       SUM(IF(E = 3, 1, 0)) AS 'E3', 
       SUM(IF(E = 4, 1, 0)) AS 'E4', 
       SUM(IF(E = 5, 1, 0)) AS 'E5', 
       SUM(IF(E = 6, 1, 0)) AS 'E6', 
       B.Qst  AS Qst_prest 
      FROM 
       tbl_A A 
      LEFT OUTER JOIN tbl_B B ON A.activated = B.prest 
      AND A.Strd = B.Strds 
      WHERE 
       E IS NOT NULL 
      AND activated = 'LavC' 
      GROUP BY 
       Strd, 
       activated, 
       qst  WITH ROLLUP; 
    
    sonova_beesh   Strd   activated   E1  E2  E3  E4  E5  E6  Qst_prest
         2         BBB    LavC        0   0   0   0   2   0   134
         2         BBB    LavC        0   0   0   0   2   0   NULL
         2         BBB    NULL        0   0   0   0   2   0   NULL
         2         CCC    LavC        0   0   0   2   0   0   99
         2         CCC    LavC        0   0   0   2   0   0   NULL
         2         CCC    NULL        0   0   0   2   0   0   NULL
         1         DDD    LavC        0   0   0   0   1   0   75
         1         DDD    LavC        0   0   0   0   1   0   NULL
         1         DDD    NULL        0   0   0   0   1   0   NULL
         5         NULL   NULL        0   0   0   2   3   0   NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    thanks a lot!

IMN logo majestic logo threadwatch logo seochat tools logo