Sum, But not all columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sum, But not all columns

Author

Topic

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 12:23:12

i have some data that i want to add a total column to. I can add this total row fine except for the last column which is a percentage column (Deals_Won_Ratio below). I want this cell to be a calculation instead of a sum but i am getting errors every way i go about it..... any ideas.... my code is below... my 2 embedded selects work fine to get the initial data. Thanks so much for any ideas

select
'Total' as Region,
sum(Total) as Total,
sum(Approved) as Approved,
sum(AvgTurnaround) as AvgTurnaround,
sum(Deals_Won) as Deals_Won,
sum(Revenue) as Revenue,
Deals_Won_Ratio
from (select Region,Total, Approved, AvgTurnaround, Deals_Won, Revenue,
left(Deals_Won_Ratio,charindex('.',Deals_Won_Ratio)+1) + '%' as 'Deals_Won_Ratio'
from(select region,Total, approved, AvgTurnaround, Deals_Won, Revenue,
convert(float,Deals_Won)/(convert(float,Approved)+ convert(float,Deals_Won))*100 as Deals_Won_Ratio
from dbo.TBL_DealReg_Overall)n)
Group By Deals_Won_Ratio
order by total

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 12:32:02

I reckon you need a Table Alias name for your outermost nested SELECT

from dbo.TBL_DealReg_Overall)n) AS X
Group By Deals_Won_Ratio

Kristen

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 12:36:11

Thanks, I tried that there but its not applying the total line and my region column data has been replaced by the word 'Total'.

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 12:42:03

select
'Total' as Region,

is going to give you a first column. in your resultset, containing the text "Total"

Is that what you are referring to, or have I misunderstood?

Kristen

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 12:52:35

yes but i only want the word total to appear in the final row, and not to replace my data in the other rows. in other queries i have used this and it works, i had my query the other way around at first.... i think it makes more sense that way as it would only be adding the one row...???

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 12:58:59

quote:
Originally posted by CodingGirl

yes but i only want the word total to appear in the final row, and not to replace my data in the other rows. in other queries i have used this and it works, i had my query the other way around at first.... i think it makes more sense that way as it would only be adding the one row...???

This is my code the other way

select Region,Total, Approved, AvgTurnaround, Deals_Won, Revenue,
left(Deals_Won_Ratio,charindex('.',Deals_Won_Ratio)+1) + '%' as 'Deals_Won_Ratio'
from(select region,Total, approved, AvgTurnaround, Deals_Won, Revenue,
convert(float,Deals_Won)/(convert(float,Approved)+ convert(float,Deals_Won))*100 as Deals_Won_Ratio
from dbo.TBL_DealReg_Overall)n
order by total
union
select
'Total' as Region,
sum(Total) as Total,
sum(Approved) as Approved,
sum(AvgTurnaround) as AvgTurnaround,
sum(Deals_Won) as Deals_Won,
sum(Revenue) as Revenue,
Deals_Won_Ratio
from dbo.TBL_DealReg_Overall
order by Total
Group By Deals_Won_Ratio

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 13:06:38

If you want the individual data rows to appear, and some "extra" rows for Totals, then you need to use a UNION to combine the two resultsets into a single resultset.

You might also be able to use ROLLUP (or perhaps CUBE) to get the extra summary information.

Kristen

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 13:09:46

ok, This is what i have now .... it gives me the error "Incorrect syntax near the keyword 'union'."

select Region,Total, Approved, AvgTurnaround, Deals_Won, Revenue,
left(Deals_Won_Ratio,charindex('.',Deals_Won_Ratio)+1) + '%' as 'Deals_Won_Ratio'
from(select region,Total, approved, AvgTurnaround, Deals_Won, Revenue,
convert(float,Deals_Won)/(convert(float,Approved)+ convert(float,Deals_Won))*100 as Deals_Won_Ratio
from dbo.TBL_DealReg_Overall)n
order by total
union
select
'Total' as Region,
sum(Total) as Total,
sum(Approved) as Approved,
sum(AvgTurnaround) as AvgTurnaround,
sum(Deals_Won) as Deals_Won,
sum(Revenue) as Revenue,
sum(convert(float,Deals_Won)/(convert(float,Approved)+ convert(float,Deals_Won))*100) as Deals_Won_Ratio
from dbo.TBL_DealReg_Overall
order by Total

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 13:26:28

You can't have an ORDER BY in the first SELECT. ORDER BY can only be across ALL the SELECTs within the UNION.

...
from dbo.TBL_DealReg_Overall)n
--DEL order by total
union
...

Kristen

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 13:53:17

I've taken that out now. but getting an error on group by.....

CodingGirl
Starting Member

8 Posts

Posted - 2007-06-19 : 14:21:29

thanks for all your help... just got it to work there by trying the order slightly differently...

here is the code in case its of any help to anyone...

select Region,Total, Approved, AvgTurnaround, Deals_Won, Revenue,
left(Deals_Won_Ratio,charindex('.',Deals_Won_Ratio)+1) + '%' as 'Deals_Won_Ratio'
from
(
select Region, Total, Approved, AvgTurnaround, Deals_Won, Revenue,
convert(float,Deals_Won)/(convert(float,Approved)+ convert(float,Deals_Won))*100 as Deals_Won_Ratio
from
(select Region,Total, Approved, AvgTurnaround, Deals_Won, Revenue
from dbo.TBL_DealReg_Overall
union
(select
'Total' as Region,
sum(Total) as Total,
sum(Approved) as Approved,
sum(AvgTurnaround) as AvgTurnaround,
sum(Deals_Won) as Deals_Won,
sum(Revenue) as Revenue
from dbo.TBL_DealReg_Overall))n)m
order by Total

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources