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
[EMAIL PROTECTED]
(516) 379-0001 Office
(516) 480-1870 Mobile/Emergencies
(516) 908-4185 Fax
http://www.meitech.com/
smime.p7s
Description: S/MIME cryptographic signature
