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)
-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]
advanced group by
Hello list! little GROUP BY problem here: Table 'shipments' ID int(10) CompanyName char(50) WhatToShip char(50) TrackingNumber char(50) SerialNumber char(50) I would like to obtain the following results: CompanyName - WhatToShip - Ready - Almost - Done Foo- car - 26 - 2- 23 Foo-elephant - 43 - 0- 15 Foo-acuarium - 12 - 6- 47 Bar- mobile- 9- 0- 52 Bar- fan - 15 - 4- 43 Ready: items with empty TrackingNumber and empty SerialNumber Almost: items with popullated TrackingNumber AND empty SerialNumber Done: items with popullated TrackingNumber and popullated SerialNumber I have been reading around and trying lots of things. I believe the answer lies on how to group by an empty field. This means, I believe I can make this work if I find a way to group by a field's emptiness or not, instead of the actual content. Google is tired of seeinf me search around for ' advanced grouping by ' and so on and on. Any help will be greatly apprecieted. (Im begging for help) James
help joining tables in a query
Greetings, I'm facing a difficult query at the moment. I have tried many different queries but still not get the desired result. My case: Tables: shipped ( id, shipdate, sn); undelivered (id, undate, sn); return (id, redate, sn); I need the following output: DATE SHIPMENTS UNDELIVERED RETURNS Fri 1 may 2005 87 11 4 Sat 2 may 2005 82 17 5 ........ ... So far, Im have succesfulyl manage to get the result using just one table: DATE SHIPMENTS Fri 1 may 2005 87 Sat 2 may 2005 82 ..... With the query: SELECT DATE_FORMAT(shipdate,'%a %e %b %Y'), COUNT(shipdate) FROM shipped WHERE (YEAR(shipdate) = 2004) AND (MONTH(shipdate) = 05) GROUP BY shipdate ORDER BY shipdate DESC However, joining the 3 tables and getting the right results is being a nightmare. I have already tried different left and right joins, with no success. Please any help, hints, or light on the query will be greately appreciated. James.
problem loading dates from txt file
Hello list! Im loading rows from a txt tab separated fields file into a MySQL table. It mostly works, but the date format in one field is not the type that MySQL likes. So right now Im loading it as a char field. Yes, it is ugly and a pain to work with. I would like to some how automate it and change the time format. From - 30-May-05 To - 2005-05-30 The txt file is initially generated from an excel sheet. Im using: MySQL 4.1.11 on Red Hat 9 with kernel 2.4.20 Any ideas? James Collado
