Results 1 to 10 of 10
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: Get multiple SUMS

    Is it possible to get a separate sum on three different possible values in a single column in one query?

    For example, There is a list of car dealers. Each car dealer can have various rating value of 1, 2, 3, 4, ect.... I want to get total sales for each of the rating values.

    In other words, I would need a SUM for all the sales of car dealers with the rating of 1 and I would need a SUM for all the sales of car dealers with the rating of 2 ect... The actual problem I am having is more complex but I think this example might help me learn what I need to solve the issue.

    Thanks for any suggestions

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT rating , SUM(sales) FROM ... GROUP BY rating
    Last edited by tonkuma; 05-29-12 at 23:49.

  3. #3
    Join Date
    May 2012
    Posts
    4

    Thanks

    Thanks, although your post was the answer it wasn't what the issues. It did make me think and find the solution.

    Coldfusion allows you to group the query output then nest a second output that ungroups to get the SUM values that where grouped in the SQL query.

    <cfoutput query="recordset1">
    #cardealers#
    <cfoutput>
    (#rating# #totalRevenue#)
    </cfoutput>
    <cfoutput>

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the relationships between car dealer and rating?
    I thought that each car dealer was rated to one rating value.
    But, it might be not.

    It is desirable to show sample data and expected results from the data.
    Last edited by tonkuma; 05-30-12 at 09:41.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want an answer in coldfusion then its best to mention its a cold fusion issue NOT a MySQL issue. however this site has no active support on cold fusion issues. you may strike lucky and find someone but I suspect in reality you'd be better off asking cold fusion questions on a cold fusion forum.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2012
    Posts
    4
    Thank you Tonkuma, Yes, there can be multiple ratings per car dealer. My apologies for not stating that clearly. Is there a way to do this in MySQL? I wrote the ColdFusion part to kinda illustrate what was needed from a working solution standpoint. I failed at trying to explain it simply and intelligently... so my bad on that. I will try to come up with a simplified example that wont take an hour to work through.

    @ healdem: Thank you for your suggestion to go somewhere else, but I am looking to learn more about SQL NOT about Coldfusion. That is why I was looking to learn a SQL solution from a SQL forum. Again, thank you for your kind, warm suggestion.
    Last edited by blue-drew; 05-30-12 at 10:05.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yet your solution is pure cold fusion
    the proposed (My)SQL solution apparently worked but was dismissed
    Thanks, although your post was the answer it wasn't what the issues. It did make me think and find the solution.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2012
    Posts
    4
    @healdum Please go kick a dog or something else similar that you might find as fun. I am not interested in playing with you today.

    Again, I am looking for a MySQL solution for something that DOES have a Coldfusion solution. I didnt ignore what was helpful and correct answer to a portion of the problem. I didnt explain myself well but your comments are really unnecessary.

    This is a rather rude place so I am out. No further replies are needed since I wont be back.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blue-drew, it's too bad i missed this thread

    i do both mysql and coldfusion, and i understand nested CFOUTPUTs

    i don't understand why post#2 wasn't what you were looking for

    you might want to take another try at explaining what your actual question is, and i'll see if i can help
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by blue-drew View Post
    Thanks, although your post was the answer it wasn't what the issues. It did make me think and find the solution.

    Coldfusion allows you to group the query output then nest a second output that ungroups to get the SUM values that where grouped in the SQL query.

    <cfoutput query="recordset1">
    #cardealers#
    <cfoutput>
    (#rating# #totalRevenue#)
    </cfoutput>
    <cfoutput>
    Although I don't know Coldfusion, does it worked?

    If I thought by analogy with html, open tag("<...>") and colse tag("</...>") should be matched.
    However, your code looks open tag("<...>") and colse tag("</...>") were not matched.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •