CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

Oracle SQL - Naming a case column

select site,
case
when site='AppCircle' then (count(create_dtime)*0.4438083264)
when site='AppCircle Clips' then (count(create_dtime)*0.0096978792)
when site='BC : SponsorPay' then (count(create_dtime)*0.9620989399)
when site='BonusCoins.com : Aarki' then (count(create_dtime)*0.4612565445)
when site='Nielsen Rewards' then (count(create_dtime)*-0.6000000000)
when site ='Portal : Paymentwall' then (count(create_dtime)*0.5433541667)
when site ='Portal : RadiumOne' then (count(create_dtime)*0.0619798753)
when site ='Portal : TrialPay' then (count(create_dtime)*2.1468159204)
when site ='bonuscp_login' then (count(create_dtime)*-0.1500000000)
when site ='facebook_like' then (count(create_dtime)*2.1468159204)
when site ='iTunes' then (count(create_dtime)*-0.0300000000)
end
From player_aux_pt
Where
Trunc(Create_Dtime) >= To_Date('2012-Nov-01','yyyy-mon-dd')
And Trunc(Create_Dtime) <= To_Date('2012-Nov-30','yyyy-mon-dd')  
group by site

which results in two columns of data

Site    [insert every case statement here]

I just want that second column of information to be named "Profit"

Site    Profit

I have tried many different ways an am stuck.

Upvotes: 1

Views: 6486

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

Note: Since your CASE statement is just a basic DECODE pattern, check the link for a concise alternative.

select site,
       count(create_dtime)
     * DECODE(site, 'AppCircle', 0.4438083264,
                    'AppCircle Clips', 0.0096978792,
                    'BC : SponsorPay', 0.9620989399,
                    ......) Profit
 ....

You can alias columns by giving it a name after the expression or base column name, e.g.

SELECT
    Site,
    Site ReNamedSite,
    Concat(Site,'a') "AddedAnA",
    COALESCE(Site,Address) AS "Two Words"
...

Notes:

  1. The keyword AS is optional
  2. Usage of double quotes is optional, unless you use multiple words

i.e.

select site,
case
when site='AppCircle' then (count(create_dtime)*0.4438083264)
when site='AppCircle Clips' then (count(create_dtime)*0.0096978792)
when site='BC : SponsorPay' then (count(create_dtime)*0.9620989399)
when site='BonusCoins.com : Aarki' then (count(create_dtime)*0.4612565445)
when site='Nielsen Rewards' then (count(create_dtime)*-0.6000000000)
when site ='Portal : Paymentwall' then (count(create_dtime)*0.5433541667)
when site ='Portal : RadiumOne' then (count(create_dtime)*0.0619798753)
when site ='Portal : TrialPay' then (count(create_dtime)*2.1468159204)
when site ='bonuscp_login' then (count(create_dtime)*-0.1500000000)
when site ='facebook_like' then (count(create_dtime)*2.1468159204)
when site ='iTunes' then (count(create_dtime)*-0.0300000000)
end Profit
From player_aux_pt
Where
Trunc(Create_Dtime) >= To_Date('2012-Nov-01','yyyy-mon-dd')
And Trunc(Create_Dtime) <= To_Date('2012-Nov-30','yyyy-mon-dd')  
group by site

Upvotes: 3

Larry Lustig
Larry Lustig

Reputation: 51000

You can rename (or name) columns and expressions in SQL using AS:

CASE 
  WHEN. . .
  WHEN. . .
END AS Profit

However, using a CASE expression in that way is not very extensible. Consider moving the multiplication factor to another table, keyed on site and then JOINing that table into the query.

Upvotes: 4

Related Questions