Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: 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
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT
    	Qst,
    	IFNULL(Strd, 'Tot') AS Strd,
    	activated AS Dimension,
    ...

Posting Permissions

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