SQL multiple aliases alongside query

Question

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>

Answer 1

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.

Answer 2

As Adam said, this is an SQL question.

From the question, I take it that you want to get items in the trolley, the total cost and the total number (total amount) of items being bought.

From my experience and little digging, you cannot do all that in a single SQL statement. Rather, you need two statements. First to get items in trolley and the second will combine total cost and total amount just as you did in the SQL in your question because they are both aggregate functions.

So remove that * in your SQL and create a new SQL.