Free Web Developer Tools
Advanced Search

Thread: Clause with rollup

    May 11th, 2013, 06:51 AM
    #1
  1. No Profile Picture
    cms9651-2
    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. May 11th, 2013, 07:46 AM
    #2
  2. r937's Avatar
    r937
    SQL Consultant
    Devshed Supreme Being (6500+ posts)
    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    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
    3. May 13th, 2013, 03:49 AM
    #3
  3. No Profile Picture
    cms9651-2
    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');
    4. May 13th, 2013, 04:26 AM
    #4
  4. r937's Avatar
    r937
    SQL Consultant
    Devshed Supreme Being (6500+ posts)
    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    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
    5. May 13th, 2013, 05:18 AM
    #5
  5. No Profile Picture
    cms9651-2
    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 |
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
    |       2 | BBB  | LavC      | 0  | 0  | 0  | 0  | 2  | 0  | 2     | 5.0        |       134 |
    |       2 | CCC  | LavC      | 0  | 0  | 0  | 2  | 0  | 0  | 2     | 4.0        |        99 |
    |       1 | DDD  | LavC      | 0  | 0  | 0  | 0  | 1  | 0  | 1     | 5.0        |        75 |
    |       5 | Tot  | Tot       | 0  | 0  | 0  | 2  | 3  | 0  | 5     | 4.6        |       308 |
    +-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+ 
    6. May 13th, 2013, 06:04 AM
    #6
  6. r937's Avatar
    r937
    SQL Consultant
    Devshed Supreme Being (6500+ posts)
    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    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
    7. May 13th, 2013, 06:21 AM
    #7
  7. No Profile Picture
    cms9651-2
    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:
    mysql> 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;
    +-----+------+-----------+----+----+----+----+----+----+-----------+
    |     Qst | 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  |       134 |
    |       2 | BBB  | NULL      | 0  | 0  | 0  | 0  | 2  | 0  |       134 |
    |       2 | CCC  | LavC      | 0  | 0  | 0  | 2  | 0  | 0  |        99 |
    |       2 | CCC  | LavC      | 0  | 0  | 0  | 2  | 0  | 0  |        99 |
    |       2 | CCC  | NULL      | 0  | 0  | 0  | 2  | 0  | 0  |        99 |
    |       1 | DDD  | LavC      | 0  | 0  | 0  | 0  | 1  | 0  |        75 |
    |       1 | DDD  | LavC      | 0  | 0  | 0  | 0  | 1  | 0  |        75 |
    |       1 | DDD  | NULL      | 0  | 0  | 0  | 0  | 1  | 0  |        75 |
    |       5 | NULL | NULL      | 0  | 0  | 0  | 2  | 3  | 0  |        75 |
    +-----+------+-----------+----+----+----+----+----+----+-----------+
    10 rows in set 
    8. May 13th, 2013, 07:27 AM
    #8
  8. r937's Avatar
    r937
    SQL Consultant
    Devshed Supreme Being (6500+ posts)
    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    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
    9. May 13th, 2013, 07:36 AM
    #9
  9. No Profile Picture
    cms9651-2
    Registered User
    Devshed Newbie (0 - 499 posts)
    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    thanks a lot!
« Previous Thread | Next Thread »
IMN logo majestic logo threadwatch logo seochat tools logo