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>

Can you provide some sample data and the results that you want? Also, what are "bog standard column names"? — Gordon Linoff, Aug 16 '13 at 10:40
Ones that you would expect within a trolley table, links to product, sizes etc. And it's not the data im bothered about it's how you go about identifying multiple aliases within one query. The rest is currently irrelevant. — Banny, Aug 16 '13 at 10:43
Other than this being called with a CFQUERY tag, has the question got anything to do with ColdFusion? Sounds like an SQL question to me..? — Adam Cameron, Aug 16 '13 at 11:08
this should point you in the right direction http://stackoverflow.com/questions/4267929/whats-the-best-way-to-join-on-the-same-table-twice — Matt Busche, Aug 16 '13 at 12:50
@LeeB - You will get a faster answer if you include a sample of the data and the results you want to see. Either post a plain dump or use http://sqlfiddle.com . Also, with sql questions, always include your DBMS in your question tags. (Looks like you are using some version of sql server..?) — Leigh, Aug 16 '13 at 13:27

score 1 · Accepted Answer · answered Aug 16 '13 at 21:21

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.

Another way to do this, possibly faster but less intuitive is ArraySum(myTrolley["TotalItemCost"]) — Dan Bracuk, Aug 19 '13 at 00:46
I like this answer, very productive and informative. And yes I agree, the one thing i'm looking to do on this project is decrease the amount that I make database calls. I never knew you could run queries of queries so this is very helpful. — Banny, Aug 20 '13 at 10:50

score -1 · Answer 2 · edited Aug 16 '13 at 19:52

-1

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.

edited Aug 16 '13 at 19:52

Leigh

28,765
10
55
103

answered Aug 16 '13 at 19:45

yomexzo

665
1
6
22

@Leigh you mind acknowledging the fact that my answer is helpful by upping the vote? Thanks. – yomexzo Aug 17 '13 at 12:51
Asking for votes is very undignified. I've downvoted accordingly. – Dan Bracuk Aug 19 '13 at 00:45
@DanBracuk like seriously? You might consider it so but I do not as I want the votes so I can contribute better. Who down votes a correct answer. smh – yomexzo Aug 19 '13 at 09:25
1

@yomexzo - While I would not down vote this post, it is poor etiquette to "ask" for votes. Better to let people decide for themselves based on the post content. In this case it could have been improved by an explanation of *why* both cannot be performed in a single statement. ie that `select *` and aggregate functions work at cross purposes. – Leigh Aug 20 '13 at 14:14

SQL multiple aliases alongside query

2 Answers2