CODEWITHSUNDEEP
sqldatabaseselect
dido
dido

Reputation: 3407

How to use alias and sum them together in select query

I have the following queries below. I'd like to use the aliases and add them together but SQL does not allow that. Any suggestions on how I can go about this without repeating the queries again? Using Microsoft SQL Server 2008

SELECT    
SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) As space,
SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END) As qpr,

(space + qpr) As result
FROM rg_fin As rg JOIN...../*query goes on*/

Upvotes: 3

Views: 12081

Answers (4)

Taryn
Taryn

Reputation: 247710

If you want to use the alias, then you will need to wrap the query inside of another query:

SELECT space
    , qpr
    , space + qpr as result
FROM
(
    SELECT    
    SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) As space,
    SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END) As qpr,

    (space + qpr) As result
    FROM rg_fin As rg JOIN...../*query goes on*/
) x

Or if you do not need to use the alias, then you can just repeat the fields:

SELECT    
    SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) As space,
    SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END) As qpr,

    (SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) 
    + SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END)) As result
FROM rg_fin As rg JOIN...../*query goes on*/

Upvotes: 1

podiluska
podiluska

Reputation: 51494

You could wrap the query as a subquery

SELECT *,
  (space + qpr) As result 
FROM
(
  SELECT     
  SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) As space, 
  SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END) As qpr
  ....
 ) subquery

Upvotes: 5

Madhivanan
Madhivanan

Reputation: 13700

Use derived table

SELECT 
    space,qpr,(space + qpr) As result 
FROM
(
SELECT     
SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) As space, 
SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END) As qpr, 

(space + qpr) As result 
FROM rg_fin As rg JOIN...../*query goes on*/ 
) as t

Upvotes: 0

Jordão
Jordão

Reputation: 56477

You can do this with a subquery:

select 
  space, 
  qpr, 
  (space + qpr) As result
from (
  select
  SUM(CASE WHEN rg.category = 'Space' THEN ((rg.score*20)) END) As space,
  SUM(CASE WHEN rg.category = 'QPR' THEN ((rg.score*20)) END) As qpr,
  FROM rg_fin As rg JOIN...../*query goes on*/
) as a

Upvotes: 1

Related Questions