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 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 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]
Re: Complex query. (It's killing me)
Shipped Undelivered Returned Open 12/8/2005 143 3 3 Does this mean of the 14 shipped on 12/8/2005, 3 were returned at some later date, or does it mean that you shipped 14 on 12/8/2005, and on that same day 3 unrelated shipments came back, each of which could have been shipped any time before 12/8/2005? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 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]
Re: Complex query. (It's killing me)
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 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 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]
Re: Complex query. (It's killing me)
-Original Message- From: James M. Gonzalez Sent: 12 August 2005 16:58 To: 'Scott Noyes' Subject: RE: Complex query. (It's killing me) Sorry, I will explain myself more clearly: Everyday, we ship packages, and we also receive some packages. The one we receive, has been shipped by us some days before. So no package can be shipped AND received in the same day. The return reasons are two: it can not be delivered ( type='undelivered') or the customer send it to us god knows why (type='customer') I hope that makes sense. Im still very stuck with this. -Original Message- From: Scott Noyes [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 16:35 To: James M. Gonzalez Cc: mysql@lists.mysql.com Subject: Re: Complex query. (It's killing me) Shipped Undelivered Returned Open 12/8/2005 143 3 3 Does this mean of the 14 shipped on 12/8/2005, 3 were returned at some later date, or does it mean that you shipped 14 on 12/8/2005, and on that same day 3 unrelated shipments came back, each of which could have been shipped any time before 12/8/2005? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex query. (It's killing me)
The original sql query returned nothing. 0 rows.
The second (with a left join) returned a record for every date avaiable,
but a 0 in all others fields. (0 shipments, 0 undelivered, 0 returned, 0
open, etc)
Im not sure if we can solve this with a join, since each colum does have
a different set of conditions (like where clausules)
James
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 12 August 2005 16:41
To: [EMAIL PROTECTED]
Cc: James M. Gonzalez; mysql@lists.mysql.com
Subject: Re: Complex query. (It's killing me)
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 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 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]
Re: Complex query. (It's killing me)
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]
