SUM field in Query

[weazy]

I am trying to select a subset of records from my database based on a particular parameter and then display all those results. In addition to displaying the results, I want a summation of a field, but a summation for the field for just that subset of records:

SELECT name, amount, SUM(amount) AS total FROM sillyhistory WHERE type_id = 1 GROUP BY name";

so that SUM(amount) is not supposed to sum the entire field amount, but just the amounts for that particular query .....

thanks.

[devinemke]

you want every record returned to include the sum of all the amounts in the record set? if your RDBMS supports sub-queries then you could do:

SELECT name, amount, (SELECT SUM(amount) FROM sillyhistory WHERE type_id = 1) AS total FROM sillyhistory WHERE type_id = 1

[weazy]

my database does not like that query..... is there a way to do it with two queries?

[devinemke]

SELECT name, amount FROM sillyhistory WHERE type_id = 1

SELECT SUM(amount) AS total FROM sillyhistory WHERE type_id = 1