How to sum a column in a SQL query

Can someone tell me how to sum values in a column from a resultant Query. For example, consider this query which in part
calls a view named "Rpt_OverallIndMarket"

select * from Rpt_OverallIndMarket order by MarketId,Year,Quarter

The resultant query from the previous query will contain a column named TotalSquareFeet. How do I sum all of the TotalSquareFeet values in
that column?

Do you just need the sum or you need the data + sum? If you need both perhaps your front end is best to calculate the sum. Most controls like datagrid etc have features to calculate a sum. If thats not possible you might need to use a GROUP BY in the query but that might affect the results.

|||

Dont know exactly if this is what you want but this is what I understood:

SELECT Sum(ALL(columnname)) FROM table

|||

select sum(TotalSquareFeet) from Rpt_OverallIndMarket returns 1 row with 1 column, the sum of total square feet
----------
select sum(TotalSquareFeet), MarketId, Year,Quarter from Rpt_OverallIndMarket group by MarketId,Year,Quarter
returns 1 row per MarketId, Year, Quarter with the sum across that MarketId, Year and Quarter (IOW, the subtotals).
----------
select 'Total ' as SubTotal, sum(TotalSquareFeet), max(MarketeId), max(Year) ,max(Quarter) from Rpt_OverallIndMarket
union all
select 'Detail', TotalSquareFeet, MarketId, Year,Quarter from Rpt_OverallIndMarket
order by 1, 2, 3,4

returns detail and total lines in 1 answer set, ordered by the first 4 columns. Those max() functions are just place holders since a union requires the same number and types of fields in all parts of the union

Detail 10 a 2000 3
Detail 15 a 2000 2
Detail 8 b 2001 1
Total 33 b 2001 3 <-- The 'b 2001 3' are just place holders, you can ignore them

My point in giving this last example is that you can use subtotaling and unions to do a lot of the subtotaling work for you -- all you need to do is include a literal column (eg, "Detail", "Total") telling your program what the row represents.

|||

Did you get the answer you wanted yet?

The two choices in the posts above are basically use theSUM function in SQL with a GROUP BY clause, or to do it yourself in code.

If you're not familiar with grouping, I'd recommend you read:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx