Can't understand why this SQL is wrong

[Bobby Hall]

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

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?

[tejash]

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

[r937]

...the query returns only one row. Why?

because you forgot the GROUP BY clause