mrivero1961 (3/28/2013)
Thank you for help.I've modified the query, but the output not change:
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) IN ('QM', 'QI', 'QO', '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 |
+-------------+--------+--------+
It wasn't supposed to change the output. It is however a lot easier to read. It did provide a query that will actually work though. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/