Lynn Pettis (3/28/2013)
Start with something like this:
select
idDGIG,
count(idDGIG) as Number
FROM (
SELECT
LEFT (idDGIG, 2) AS idDGIG,
FROM
TABLE_LONG
WHERE
LEFT (CA.idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
UNION ALL
SELECT
LEFT (idDGIG, 2) AS idDGIG,
FROM
TABLE_SHORT
WHERE
LEFT (CA.idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY))
GROUP BY
idDGIG
thank you, but:
SELECT
idDGIG,
count(idDGIG) as Number
FROM (
SELECT
LEFT (idDGIG, 2) AS idDGIG,
FROM
TABLE_LONG
WHERE
LEFT (CA.idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY)
UNION ALL
SELECT
LEFT (idDGIG, 2) AS idDGIG,
FROM
TABLE_SHORT
WHERE
LEFT (CA.idDGIG, 2) IN ('QM', 'QI', 'QO', 'QS')
AND DATE_START = DATE_ADD(CURDATE(), INTERVAL - 1 DAY))
GROUP BY
idDGIG
) x;
+--------+--------+
| sGIG | number |
+--------+--------+
| QO | 5 |
+--------+--------+