Something like this
SELECT CompanyName,
WhatToShip,
SUM(IF(TrackingNumber = '',
IF(SerialNumber = '',
1,
0),
0)
) AS READY,
SUM(IF(TrackingNumber <> '',
IF(SerialNumber = '',
1,
0),
0)
) AS Almost,
SUM(IF(TrackingNumber <> '',
IF(SerialNumber <> '',
1,
0),
0)
) AS Done
FROM shipments
GROUP BY 1,2
-----Original Message-----
From: James M. Gonzalez [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 03, 2005 5:59 AM
To: [email protected]
Subject: 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
