locked
Query join RRS feed

  • Question

  • User1872364149 posted

    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:

    mysql> SELECT
    COALESCE (idDGIG, 'Tot') AS sGIG,
    `NUMBER`
    FROM
    (
    SELECT
    LEFT (idDGIG, 2) AS idDGIG,
    COUNT(idDGIG) AS NUMBER
    FROM
    TABLE_LONG
    WHERE
    1
    AND (
    LEFT (idDGIG, 2) LIKE '%QM%'
    OR LEFT (idDGIG, 2) LIKE '%QI%'
    OR LEFT (idDGIG, 2) LIKE '%QO%'
    OR LEFT (idDGIG, 2) LIKE '%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:

    mysql> SELECT
    COALESCE (idDGIG, 'Tot') AS sGIG,
    NUMBER
    FROM
    (
    SELECT
    LEFT (idDGIG, 2) AS sGIG,
    COUNT(idDGIG) AS NUMBER
    FROM
    TABLE_SHORT
    WHERE
    1
    AND (
    LEFT (idDGIG, 2) LIKE '%QM%'
    OR LEFT (idDGIG, 2) LIKE '%QI%'
    OR LEFT (idDGIG, 2) LIKE '%QO%'
    OR LEFT (idDGIG, 2) LIKE '%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 (1144), why?
    Can you help me.
    Thanks in advance.

    mysql> SELECT
    DATE_START,
    COALESCE (idDGIG, 'Tot') AS sGIG,
    `NUMBER`
    FROM
    (
    SELECT
    CA.DATE_START AS DATE_START,
    LEFT (CA.idDGIG, 2) AS sGIG,
    COUNT(CA.idDGIG) + COUNT(A.EVENT) AS NUMBER
    FROM
    TABLE_LONG CA
    JOIN TABLE_SHORT A ON CA.DATE_START = A.DATE_START
    WHERE
    CA.DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    AND (
    LEFT (CA.idDGIG, 2) LIKE '%QM%'
    OR LEFT (CA.idDGIG, 2) LIKE '%QI%'
    OR LEFT (CA.idDGIG, 2) LIKE '%QO%'
    OR LEFT (CA.idDGIG, 2) LIKE '%QS%'
    )
    GROUP BY
    LEFT (CA.idDGIG, 2) WITH ROLLUP
    ) AS SSS;
    +-------------+--------+--------+
    | DATE_START | sGIG | NUMBER |
    +-------------+--------+--------+
    | 2013-03-27 | QI | 468 |
    | 2013-03-27 | QM | 104 |
    | 2013-03-27 | QO | 312 |
    | 2013-03-27 | QS | 260 |
    | 2013-03-27 | Tot | 1144 |
    +-------------+--------+--------+
    5 rows in set



    Thursday, March 28, 2013 4:10 PM

Answers

  • User1083584480 posted

    in that case you should use union and use below query

    SELECT
    COALESCE (idDGIG, 'Tot') AS sGIG,
    NUMBER
    FROM(

    select idDGIG, sum(NUMBER) as NUMBER
    (

    SELECT LEFT (idDGIG, 2) AS idDGIG, COUNT(idDGIG) AS NUMBER
    FROM TABLE_LONG WHERE
    1
    AND (
    LEFT (idDGIG, 2) LIKE '%QM%'
    OR LEFT (idDGIG, 2) LIKE '%QI%'
    OR LEFT (idDGIG, 2) LIKE '%QO%'
    OR LEFT (idDGIG, 2) LIKE '%QS%'
    )
    AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    GROUP BY
    LEFT (idDGIG, 2) WITH ROLLUP

    union all

    SELECT LEFT (idDGIG, 2) , COUNT(idDGIG)
    FROM TABLE_SHORT
    WHERE
    1
    AND (
    LEFT (idDGIG, 2) LIKE '%QM%'
    OR LEFT (idDGIG, 2) LIKE '%QI%'
    OR LEFT (idDGIG, 2) LIKE '%QO%'
    OR LEFT (idDGIG, 2) LIKE '%QS%'
    )
    AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    GROUP BY
    LEFT (idDGIG, 2) WITH ROLLUP
    )
    from abc
    group by idDGIG

    )AS Z

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 29, 2013 8:05 AM

All replies

  • User1083584480 posted

    Please use below query

    highlighted are the changes

    SELECT
    DATE_START,
    COALESCE (idDGIG, 'Tot') AS sGIG,
    `NUMBER`
    FROM
    (
    SELECT
    CA.DATE_START AS DATE_START,
    LEFT (CA.idDGIG, 2) AS sGIG,
    COUNT(CA.idDGIG)  AS NUMBER
    FROM
    TABLE_LONG CA
    JOIN TABLE_SHORT A ON CA.DATE_START = A.DATE_START
    and LEFT (CA.idDGIG, 2)= LEFT (A.idDGIG, 2)
    WHERE
    CA.DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    AND (
    LEFT (CA.idDGIG, 2) LIKE '%QM%'
    OR LEFT (CA.idDGIG, 2) LIKE '%QI%'
    OR LEFT (CA.idDGIG, 2) LIKE '%QO%'
    OR LEFT (CA.idDGIG, 2) LIKE '%QS%'
    )
    GROUP BY
    LEFT (CA.idDGIG, 2) WITH ROLLUP
    ) AS SSS;

    Friday, March 29, 2013 5:10 AM
  • User1872364149 posted

    thank you very much for help.

    I tried your query suggestion but the output is wrong:

    SELECT
    	DATE_START,
    	COALESCE (idDGIG, 'Tot') AS sGIG,
    	`NUMBER`
    FROM
    	(
    		SELECT
    			CA.DATE_START AS DATE_START,
    			LEFT (CA.idDGIG, 2) AS sGIG,
    			COUNT(CA.idDGIG) AS NUMBER
    		FROM
    			TABLE_LONG CA
    		JOIN TABLE_SHORT A ON CA.DATE_START = A.DATE_START
    		AND LEFT (CA.idDGIG, 2) = LEFT (A.idDGIG, 2)
    		WHERE
    			CA.DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    		AND (
    			LEFT (CA.idDGIG, 2) LIKE '%QM%'
    			OR LEFT (CA.idDGIG, 2) LIKE '%QI%'
    			OR LEFT (CA.idDGIG, 2) LIKE '%QO%'
    			OR LEFT (CA.idDGIG, 2) LIKE '%QS%'
    		)
    		GROUP BY
    			LEFT (CA.idDGIG, 2) WITH ROLLUP
    	) AS SSS;
    
    
    +------------+------+--------+
    | DATE_START | sGIG | NUMBER |
    +------------+------+--------+
    | 2013-03-27 | QI   |     18 |
    | 2013-03-27 | QM   |      4 |
    | 2013-03-27 | QO   |     96 |
    | 2013-03-27 | QS   |     40 |
    | 2013-03-27 | Tot  |    158 |
    +------------+------+--------+
    5 rows in set

    I need this output: 

    +--------+--------+
    | sGIG   | NUMBER |
    +--------+--------+
    | QI     |     11 |
    | QM     |      4 |
    | QO     |     22 |
    | QS     |     11 |
    | Tot    |     48 |
    +--------+--------+




    Friday, March 29, 2013 7:25 AM
  • User1083584480 posted

    in that case you should use union and use below query

    SELECT
    COALESCE (idDGIG, 'Tot') AS sGIG,
    NUMBER
    FROM(

    select idDGIG, sum(NUMBER) as NUMBER
    (

    SELECT LEFT (idDGIG, 2) AS idDGIG, COUNT(idDGIG) AS NUMBER
    FROM TABLE_LONG WHERE
    1
    AND (
    LEFT (idDGIG, 2) LIKE '%QM%'
    OR LEFT (idDGIG, 2) LIKE '%QI%'
    OR LEFT (idDGIG, 2) LIKE '%QO%'
    OR LEFT (idDGIG, 2) LIKE '%QS%'
    )
    AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    GROUP BY
    LEFT (idDGIG, 2) WITH ROLLUP

    union all

    SELECT LEFT (idDGIG, 2) , COUNT(idDGIG)
    FROM TABLE_SHORT
    WHERE
    1
    AND (
    LEFT (idDGIG, 2) LIKE '%QM%'
    OR LEFT (idDGIG, 2) LIKE '%QI%'
    OR LEFT (idDGIG, 2) LIKE '%QO%'
    OR LEFT (idDGIG, 2) LIKE '%QS%'
    )
    AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
    GROUP BY
    LEFT (idDGIG, 2) WITH ROLLUP
    )
    from abc
    group by idDGIG

    )AS Z

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 29, 2013 8:05 AM
  • User1872364149 posted

    thank you, I've error:

    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
    				SELECT
                                    LEFT (idDGIG, 2) AS idDGIG, 
                                    COUNT(idDGIG) AS NUMBER
    	' at line 8
    



    Friday, March 29, 2013 9:38 AM