[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Sub query Help
From:       "Gustafson, Tim" <tjg () meitech ! com>
Date:       2005-01-31 13:15:28
Message-ID: DA749670ABB17C4994D03B34889179D82289CA () fee ! meitech ! com
[Download RAW message or body]


Hello

I have written a report generator in PHP that handles creating and
paginating the results from queries into a nice, user-friendly, HTML
format.  Because it's a generic report generator, it has some quirks
about it, but I have managed to modify queries enough so that it works
in almost all cases for me.

There is one exception:

My database has the following tables:

States - A list of US States
Orders - A list of orders that have been placed
OrderItems - A list of the individual items that have been purchased on
each order

My report generator has create the following query to view a summary of
how much merchandise was purchased by customers, grouped by state:

select (select Abbreviation from States where States.ID =
Orders.BillingState) as `State`,
       count(*) as `Count`,
       (select sum(Price)
        from OrderItems
        where OrderItems.Order = Orders.ID) as `Price`,
       `BillingState`
from Orders
group by `BillingState`
order by `State`

The query executes, but it does not return the correct results.  It
seems to be returning the totals for one individual order rather than
the sum of all the orders.

I know there are ways I can change the overall query to fix the problem,
but I can't just change that overall query: remember, this is in a
report generator, and arguably a simple one at that.  I have to fit
everything I need to do into the column sub queries.  Is this possible?
Is there any way to modify the sub query so that it will include all
orders rather than just one?

Thanks!

Tim Gustafson
MEI Technology Consulting, Inc
tjg@meitech.com
(516) 379-0001 Office
(516) 480-1870 Mobile/Emergencies
(516) 908-4185 Fax
http://www.meitech.com/ 


["smime.p7s" (application/x-pkcs7-signature)]

[prev in list] [next in list] [prev in thread] [next in thread]