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:


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:


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:


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

Can you help me please.
Thanks in advance.


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

hello,

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

try this

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

thank you very much for help.

I tried your query suggestion but the output is wrong:

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:

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

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.

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