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]

Reply via email to