Hi there, hope in your help.In my DB 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: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:SELECT COALESCE (idDGIG, 'Tot') AS sGIG, NUMBERFROM ( 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.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