SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problem with query union

    Hi there, hope in your help.

    In my DB MySQL I've two tables: TABLE_LONG and TABLE_SHORT.
    The two tables are equal but in TABLE_LONG a recording long events and in TABLE_SHORT a recording short events.

    If count number of long events, I've this output:
    Code:
    mysql> SELECT
    	COALESCE (idDGIG, 'Tot') AS sGIG,
    	`NUMBER`
    FROM
    	(
    		SELECT
    			LEFT (idDGIG, 2) AS idDGIG,
    			COUNT(idDGIG) AS NUMBER
    		FROM
    			TABLE_LONG
    		WHERE
                    LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    		GROUP BY
    			LEFT (idDGIG, 2) WITH ROLLUP
    	) AS Q;
    +--------+--------+
    | sGIG   | NUMBER |
    +--------+--------+
    | QI     |      9 |
    | QM     |      2 |
    | QO     |      6 |
    | QS     |      5 |
    | Tot    |     22 |
    +--------+--------+
    5 rows in set
    If count number of short events, I've this output:
    Code:
    mysql> SELECT
    	COALESCE (idDGIG, 'Tot') AS sGIG,
    	NUMBER
    FROM
    	(
    		SELECT
    			LEFT (idDGIG, 2) AS sGIG,
    			COUNT(idDGIG) AS NUMBER
    		FROM
    			TABLE_SHORT
    		WHERE
                    LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    		GROUP BY
    			LEFT (idDGIG, 2) WITH ROLLUP
    	) AS Z;
    +--------+-----------+
    | sGIG   | NUMBER    |
    +--------+-----------+
    | QI     |         2 |
    | QM     |         2 |
    | QO     |        16 |
    | QS     |         6 |
    | Tot    |        26 |
    +--------+-----------+
    5 rows in set
    Now I need tried join two tables with this query; I think in output total events ( long + short ): 22+26 = 48.
    Instead I've this wrong output, why?

    I need this output:
    Code:
    +--------+--------+
    | sGIG   | NUMBER |
    +--------+--------+
    | QI     |     11 |
    | QM     |      4 |
    | QO     |     22 |
    | QS     |     11 |
    | Tot    |     48 |
    +--------+--------+
    Can you help me please.
    Thanks in advance.

    Code:
    SELECT
    	COALESCE (idDGIG, 'Tot') AS sGIG,
    	`NUMBER`
    FROM
    	(
    		SELECT
    			LEFT (idDGIG, 2) AS idDGIG,
    			COUNT(idDGIG) AS NUMBER
    		FROM
    			TABLE_LONG
    		WHERE
    		LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    		GROUP BY
    			LEFT (idDGIG, 2)
    		UNION ALL
    			SELECT
    				LEFT (idDGIG, 2) AS sGIG,
    				COUNT(idDGIG) AS NUMBER
    			FROM
    				TABLE_SHORT
    			WHERE
    			LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    			AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    	) AS Q
    GROUP BY
    	LEFT (idDGIG, 2) WITH ROLLUP;
    
    
    
    +------+--------+
    | sGIG | NUMBER | 
    +------+--------+
    | QI   |      9 | 
    | QM   |      2 | 
    | QO   |      6 | 
    | QS   |      5 | 
    | QS   |      5 | 
    +------+--------+
    5 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello,

    You'll want it to do the union first, then the grouping.

    try this

    Code SQL:
    SELECT COALESCE(idDGIG, 'Tot'),
      COUNT(idDGIG) FROM (
    		SELECT
    			LEFT (idDGIG, 2) AS idDGIG
    		FROM
    			TABLE_LONG
    		WHERE
    		LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    		UNION ALL
    			SELECT
    				LEFT (idDGIG, 2) AS sGIG
    			FROM
    				TABLE_SHORT
    			WHERE
    			LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    			AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    ) AS both_tables
      GROUP BY
      			idDGIG WITH ROLLUP

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you very much for help.

    I tried your query suggestion but the output is wrong:

    Code:
    select coalesce(idDGIG, 'Tot'),
      count(idDGIG) from (
    		SELECT
    			LEFT (idDGIG, 2) AS idDGIG
    		FROM
    			TABLE_LONG
    		WHERE
    		LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    		AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    		UNION ALL
    			SELECT
    				LEFT (idDGIG, 2) AS sGIG
    			FROM
    				TABLE_SHORT
    			WHERE
    			LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
    			AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    ) as both_tables
      GROUP BY
      			idDGIG WITH ROLLUP
    
    
    +------+-------+
    | sGIG |NUMBER |
    +------+-------+
    | QI   |     2 |
    | QM   |     2 |
    | QO   |    16 |
    | QS   |     8 |
    | Tot  |    28 |
    +------+-------+
    5 rows in set
    I need this output:

    Code:
    +--------+--------+
    | sGIG   | NUMBER |
    +--------+--------+
    | QI     |     11 |
    | QM     |      4 |
    | QO     |     22 |
    | QS     |     11 |
    | Tot    |     48 |
    +--------+--------+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm I'm not sure what is wrong. could you try the 2 inner selects and see if they return your expected records?

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jurn View Post
    hmm I'm not sure what is wrong. could you try the 2 inner selects and see if they return your expected records?
    Now working, thank you.
    Code:
    SELECT Coalesce( sGIG, 'Tot' ) as sGIG, Sum(Number) as Num
    FROM (
      SELECT LEFT (idDGIG, 2) AS sGIG, COUNT(idDGIG) AS NUMBER
      FROM TABLE_LONG
      WHERE
      LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
      AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
      GROUP BY sGIG
    
      UNION ALL
    
      SELECT LEFT (idDGIG, 2) AS sGIG, COUNT(idDGIG) AS NUMBER
      FROM TABLE_SHORT
      WHERE
      LEFT (idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
      AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
      GROUP BY sGIG
    ) AS sums
    Group by sGIG WITH ROLLUP;
    
    +--------+--------+
    | sGIG   | NUMBER |
    +--------+--------+
    | QI     |     11 |
    | QM     |      4 |
    | QO     |     22 |
    | QS     |     11 |
    | Tot    |     48 |
    +--------+--------+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

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