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 2012 Forums
 Transact-SQL (2012)
 SUM on AS column

Author  Topic 

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2013-07-05 : 11:37:58
Hi, I want to group by MVP, but I get error because I need to sum MVP first. So put sum(MVP), but doesn't work.. Could anyone tell me how to do that ? Thanks.


select
LS,
(CAST(IV AS float) +
CAST (EH as float) +
CAST(OE as float)) as MVP
from Table1
Group by LS

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-05 : 11:44:41
quote:
Originally posted by dbonneau

Hi, I want to group by MVP, but I get error because I need to sum MVP first. So put sum(MVP), but doesn't work.. Could anyone tell me how to do that ? Thanks.


select
LS,
(CAST(IV AS float) +
CAST (EH as float) +
CAST(OE as float)) as MVP
from Table1
Group by LS



The way the query is written, it will have two columns, LS and MVP, with one row for each distinct value of LS.

When you say you want to group by MVP, what should the output look like? For example, if your current output is like shown below, what is the new output you are expecting?
LS  MVP
1 11.7
2 11.7
3 15.2
4 19.1
Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2013-07-05 : 11:52:19
quote:
Originally posted by James K

quote:
Originally posted by dbonneau

Hi, I want to group by MVP, but I get error because I need to sum MVP first. So put sum(MVP), but doesn't work.. Could anyone tell me how to do that ? Thanks.


select
LS,
(CAST(IV AS float) +
CAST (EH as float) +
CAST(OE as float)) as MVP
from Table1
Group by LS



The way the query is written, it will have two columns, LS and MVP, with one row for each distinct value of LS.

When you say you want to group by MVP, what should the output look like? For example, if your current output is like shown below, what is the new output you are expecting?
LS  MVP
1 11.7
2 11.7
3 15.2
4 19.1




Hi, I am sorry.. What I meant was group by LS. So, I need to sum on MVP to get an output something like you described.. thank you

EX.

LS MVP
James 23
John 11
David 50

Thanks.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-05 : 12:22:38
Where are you getting the names
James, John and David in you output?

LS MVP
James 23
John 11
David 50

Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2013-07-05 : 12:23:42
Yes. LS is varchar. Thanks
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-05 : 12:28:05
Is this what you want:

[CODE]
SELECT LS, SUM(MVP) FROM
(select
LS,
(CAST(IV AS float) +
CAST (EH as float) +
CAST(OE as float)) as MVP
from Table1)T
Group by LS


[/CODE]
Go to Top of Page

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2013-07-05 : 15:18:48
quote:
Originally posted by MuMu88

Is this what you want:

[CODE]
SELECT LS, SUM(MVP) FROM
(select
LS,
(CAST(IV AS float) +
CAST (EH as float) +
CAST(OE as float)) as MVP
from Table1)T
Group by LS


[/CODE]




Thank you ! It works good !
Go to Top of Page
   

- Advertisement -