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
[email protected]
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
<[email protected]>
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]
