This should work:
SELECT FlTdate Fecha, COUNT(TotBoarded) Vuelos
FROM transactions
WHERE TxnAmount = 0
AND TotBoarded <> 0
AND Origin = 'JFK'
AND FltDate BETWEEN '2005-10-16' AND '2005-10-29'
GROUP BY FltDate
ORDER BY FltDate
UNION ALL
SELECT NULL, COUNT(*) Vuelos
FROM transactions
WHERE NOT (
TxnAmount = 0
AND TotBoarded <> 0
AND Origin = 'JFK'
AND FltDate BETWEEN '2005-10-16' AND '2005-10-29'
)
although the performance probably won't be very good because of the NOT expression in the UNIONed result...
also, perhaps this might work better, although it's uglier IMHO:
SELECT
CASE WHEN
(FltDate BETWEEN '2005-10-16' AND '2005-10-29'
AND TxnAmount = 0
AND TotBoarded <> 0
AND Origin = 'JFK') THEN FlTdate
ELSE NULL
END CASE as Fecha
, COUNT(TotBoarded) as Vuelos
FROM transactions
GROUP BY
CASE WHEN
(FltDate BETWEEN '2005-10-16' AND '2005-10-29'
AND TxnAmount = 0
AND TotBoarded <> 0
AND Origin = 'JFK') THEN FlTdate
ELSE NULL
END as Fecha
ORDER BY Fecha;
Come to think of it, the following should be your best best:
SELECT t2.FlTdate Fecha, COUNT(t2.TotBoarded) Vuelos
FROM transactions
LEFT JOIN transactions t2
ON t1.PRIMARY_KEY_COL = t2.PRIMARY_KEY_COL # see my comment below
AND t2.TxnAmount = 0
AND t2.TotBoarded <> 0
AND t2.Origin = 'JFK'
AND t2.FltDate BETWEEN '2005-10-16' AND '2005-10-29'
GROUP BY t2.FltDate
ORDER BY t2.FltDate;
However, in the join, you need to make sure you first provide the join with all the c9olumns in the transactions table PRIMARY KEY... so if you have an ID column, use that...
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com
Edited 1 time(s). Last edit at 12/14/2005 09:59PM by Jay Pipes.