mrivero1961 (3/28/2013)
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 |
+--------+--------+
You got the best I can give you since you have not provided anything usable for us to work with, no DDL (CREATE TABLE statement(s)) for the table(s) involved, no sample data in the form of INSERT INTO statements for the table(s). In addition, you are asking for MySQL answer on a Microsoft SQL Server forum.
You want better answers? You have to either help us help you or go find a MySQL forum where people will more than likely have a better chance of helping you because they will be more familiar with the product. The best we can do IF you give us what we need is a Standard SQL (not MySQL or T-SQL) answer.