I'm currently working on a query which pulls out all the items from a trolley that a user has added. The below code demonstrates what I am trying to achieve, where I take all the trolley items and total the quantity up, aswell as multiplying the cost + quantity of each item against eachother and summing those up too. I also want to be able to call out bog standard column names from this query. I'm not too sure about how I can do this other than create 3 queries, one for the trolley itself, one for the total amount of items for the user and one for the total cost of user, however surely it can all be done in one query right?
<cfquery datasoure="#application.datasource#" name="trolley">
Select *, IsNull(Sum(trolley_amount), 0) As trolly_items, IsNull(Sum(trolley_cost * trolley_amount), 0) As trolley_totalcost
From trolley
</cfquery>
I'll give you a coldfusion answer. You can do this in one query to the DB and 1 or 2 query of a query queries. This will "look like" 2 or 3 queries in your code but in reality it will be 1 query (trip to the DB) and 2 "array filtering or aggreegating" operations. In short it would look like this:
<cfquery name="myTrolley" datasource="myDSN">
SELECT Item, quantity, cost, quantity * cost AS totalItemCost
FROM trolley
WHERE userID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#userid#"/>
<cfquery name="itemTotals" dbtype="query">
SELECT SUM(totalItemCost) AS grandTotal
FROMY myTrolley
</cfquery>
Of course your actual query will differ, but if your goal is to reduce traffic to the DB (a laudable goal that can reap dividends sometimes) then this might be the way to go. Q of a Q is pretty lean and efficient for this sort of thing - though of course it does break down when you try to get overly complex with it.