locked
[MySQL] Clause with rollup RRS feed

  • Question

  • User1872364149 posted

    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:

    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    | 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 

    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 |
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+



    Friday, May 10, 2013 5:22 AM

Answers

  • User-861818263 posted

    I don't have my sql on my system, that is why i am unable to test it. but i am wondering why it is giving only one record, i have added only one condition in your SQL of first selec statment that is 

    IF(Strd = 'Tot',SUM(Qst_prest),Qst_prest) as Qst_prest
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 13, 2013 9:44 AM

All replies

  • User-861818263 posted

    SQL should look like , please test it not tested

    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',
    			IF(Strd = 'Tot',SUM(B.Qst), 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;

    Friday, May 10, 2013 5:44 AM
  • User1872364149 posted

    Thank you but the output not change.

    Friday, May 10, 2013 5:55 AM
  • User-861818263 posted
    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,
    	IF(Strd = 'Tot',SUM(Qst_prest),Qst_prest) as 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;

    Friday, May 10, 2013 9:16 AM
  • User1872364149 posted

    thank yo, but now the output is.:

    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+
    | 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 |
    +--------+--------+--------------+----+----+----+----+----+----+-------+------------+-----------+



    Friday, May 10, 2013 10:22 AM
  • User-861818263 posted

    I don't have my sql on my system, that is why i am unable to test it. but i am wondering why it is giving only one record, i have added only one condition in your SQL of first selec statment that is 

    IF(Strd = 'Tot',SUM(Qst_prest),Qst_prest) as Qst_prest
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 13, 2013 9:44 AM