[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

get totals from query

Roxanne

3/22/2007 5:02:00 PM

I have a select statement as such
select year, month, SUM(cost) as tCost from tblCost
group by year, month


I want to get a total for each month then get a grand total. How can I
create the SQL to get the grand total of the tCost column and add the word
total in there?

so i want my output to look like this

2006 December 500
Januarary 200
Total 700

2007 March 200
June 800
Total 1000

and so on


2 Answers

Aaron [SQL Server MVP]

3/22/2007 5:14:00 PM

0

This sounds like something the presentation tier / reporting layer would be
much better able to handle than SQL.

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...





"John" <John@gmail.com> wrote in message
news:Or%23fOPKbHHA.1400@TK2MSFTNGP06.phx.gbl...
>I have a select statement as such
> select year, month, SUM(cost) as tCost from tblCost
> group by year, month
>
>
> I want to get a total for each month then get a grand total. How can I
> create the SQL to get the grand total of the tCost column and add the word
> total in there?
>
> so i want my output to look like this
>
> 2006 December 500
> Januarary 200
> Total 700
>
> 2007 March 200
> June 800
> Total 1000
>
> and so on
>


markc600

3/22/2007 5:14:00 PM

0

select case when grouping(year)=0 then cast(year as varchar(10)) else
'Total' end as year,
month,
SUM(cost) as tCost
from tblCost
group by year, month
with rollup
having grouping(year) = grouping(month)