SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't understand why this SQL is wrong

    There's something fundamental that I'm failing to get here. I have the following SQL query.

    Code SQL:
    SELECT caseid, description, amount, SUM(amount) AS billtotal
    FROM billtran 
    WHERE billtran.caseid = 2

    The table has dozens and dozens and dozens of rows where the billtran.caseid equals 2, but the query returns only one row. Why? It returns the first row where billtran.caseid equals 2, but what I want is all the rows plus the amount that is the sum of all their values. What do I need to do?

  2. #2
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not sure but give it a try with this

    SELECT caseid, description, amount, SUM(select amount from billtran as bt where bt.caseid = 2) AS billtotal
    FROM billtran
    WHERE billtran.caseid = 2

    if it works let me know i give u explanation

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Bobby Hall View Post
    ...the query returns only one row. Why?
    because you forgot the GROUP BY clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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