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