Hello list, I got a sql query that's is just beating me (5-0). Have have read here and there, and MySQL Query Browser is just fed up with all the tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me): [shipments] ID int DateShipped DATE [returns] ID INT DateReturned DATE Type ENUM('undelivered','customer') Status ENUM('open','close') Second, the expected result: Shipped Undelivered Returned Open 12/8/2005 14 3 3 3 11/8/2005 13 1 1 1 10/8/2005 22 3 8 7 09/8/2005 16 9 5 6 08/8/2005 28 3 6 7 Explanation of field columns: Shipped: rows from [shipments] that have the field 'DateShipped' populated. Undelivered: rows from [returns] that have Type = 'undelivered' Returnded: rows from [returns] that have Type = 'customer' Open: rows from [returns] that have Status = 'open' Third, the sql query I have came out with so far is (my non-working best solution) SELECT shipments.DateShipped, SUM(CASE WHEN shipments.DateShipped IS NOT NULL THEN 1 ELSE 0 END) AS shipped SUM(CASE WHEN returns.DateReturned='undelivered' THEN 1 ELSE 0 END) AS undelivered, SUM(CASE WHEN returns.DateReturned='customer' THEN 1 ELSE 0 END) AS returned, SUM(CASE WHEN returns.Status='open' THEN 1 ELSE 0 END) AS open FROM shipments, returns GROUP BY shipments.DateShipped ORDER BY shipments.DateShipped DESC LIMIT 5 That's it. Im SO stuck with this query. I would really really appreciate any help, hints, links, or ideas about it. James G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]