Sorry - I think you need a LEFT JOIN or it won't count shipments which are not returned.
Alec [EMAIL PROTECTED] 12/08/2005 16:38 To [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: Complex query. (It's killing me) Just in the spirit of refining my own skills, here is how I would tackle the problem. It parses, but I haven't populated the tables so I don't know if it works: SELECT s.dateshipped, COUNT(r.type="undelivered"), COUNT(r.type = "customer"), COUNT(r.status="open") FROM shipments s JOIN returns r ON s.id = r.id GROUP BY s.dateshipped ORDER BY s.dateshipped DESC ; Does this do anything worthwhile? "James M. Gonzalez" <[EMAIL PROTECTED]> 12/08/2005 16:16 To <mysql@lists.mysql.com> cc Subject Complex query. (It's killing me) 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]