Is it possible to run a query on a resultset?? If not, what's the best way to run a query on a query. I know that one way would be to save the resultset as a new table and then run a query on it, but there must be an easier way. For example: If you have a table with the following columns: Date, Booking Amount, Location (say), Customer. So, if a customer makes three bookings there are three records in the table. What would be the best way to get a report that gives you bookings for a particular month for a particular customer. You would have to group the dates to get the months and you would also have to group the bookings by customer. Hence, you will need to have two passes (or a query running on a query) on the data. I hope I have explained what I am trying to do well?? (And that someone has a simple solution).
Can you not just SELECT * WHERE customer_id = 'x' AND date > 'input_start_date' AND date < 'input_end_date'; ? Alternatively, load all the customer x's order into an array and sort in on the PHP side of it by date.
Won't something like this work? SELECT Customer, Month(tDate), SUM(nBooking) FROM Bookins GROUP BY Customer,Month(tDate) ORDER BY 1, 2 Where Month() is the function to get the month from a date/datetime(I'm too lazy to find the MySQL function for that right now). That will get you a listing with the total booking $$$ per month for each customer, sorted by customer and then by month.... -- Derek
Hi guys, Thanks for the replies. I don't think I gave a very good example for what I am trying to do, as in my case one query is just not enough to get the results. So, let's try this example: The table contains a line of data for each product sold (Say) Customer | Location | Product | Category | Amount | Date --------------------------------------------------------- Cust1 | America | Blue Widget | Widgets | $35,000 | 25/02/05 Cust2 | Australia |Clear bulb | Light Bulb | $5,000 | 01/03/05 Cust2 | Australia | Big bulb | Light Bulb | $10,000 | 05/04/05 Now, the second and third line belong to the same order. So, first I need to group these by customer to get a list of all sales, before running any other query on it. Ex: If I wanted to calculate the average amount for an order. I would first need to group all these lines by customer, giving me two orders: $35k and $15k and then I would need to run another query on this data to get the average of $25k. If I try running an average directly on the raw data, I would get the average value of sold items and not average order. Anyway, going back to my original question: Is it possible to run a query on a resultset?
Why not group by your orderID column, then? And if you don't have one, shame on you-- go read a book on DB design. ;-) Anyways, to answer your question, yes. This works in MSSQL. I think MySQL supports it as well. SELECT Cust, SUM(nTotal) FROM (SELECT Cust, Something, SUM(nTotal) FROM Orders GROUP BY Something, Cust) AS temp GROUP BY Cust -- Derek
lol! I do have a dozen other columns to work with! So, a nested SQL query ....... hmmmm, interesting! Maybe I should buy a book on SQL commands instead. I will try this out this weekend. Thanks for your help!
I think we can use query inside query like this select * from tbl where id in (select * from tbl wehre conditino)