> mysql Ver 12.22 Distrib 4.0.20, for mandrake-linux-gnu (i586)
With 4.1, it might have been a little easier using some subqueries.
But with 4.0, I don't think we can get the results you're looking for
in a single query, without some really nasty setup. Part of the issue
is that we need to join the tables using the date column, and if you
have dates where you didn't ship anything but got returns, and others
where you didn't get returns but you did ship, we'll be missing rows.
However, we can get the results you want in three queries, using
temporary tables. Or you could do it in two, if you want to join them
together using your favorite client-side language.
CREATE TEMPORARY TABLE myTemp
SELECT
dateShipped AS date,
COUNT(*) AS shipped,
0 AS undelivered,
0 AS returned,
0 AS open
FROM shipped
GROUP BY dateShipped;
INSERT INTO myTemp
SELECT
dateReturned AS date,
0 AS shipped,
SUM(type='undelivered') AS undelivered,
SUM(type='customer') AS returned,
SUM(status='open') AS open
FROM returns
GROUP BY dateReturned;
SELECT
date,
SUM(shipped) AS shipped,
SUM(undelivered) AS undelivered,
SUM(returned) AS returned,
SUM(open) AS open
FROM myTemp
GROUP BY date;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
