Adding up Alias with other MySQL column value

Question

I have one problem with this query; I can't seem to get ((total + rec_host) / 2) AS total2 to work. How would I go about this procedure without doing:

((((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) + rec_host ) / 2)

Here's my Query:

   SELECT host_name, 
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) AS total,
       ((total + rec_host) / 2) AS total2
   FROM lhr_reviews 
   GROUP BY host_name
   ORDER BY total 
   DESC LIMIT 0,10

Mahmoud Gamal · Accepted Answer · 2012-12-11 08:47:41Z

5

Use a subquery like so:

SELECT 
  host_name, 
  cnt1, 
  cnt2,
  cnt3,
  cnt4,
  cnt5,
  cnt6,
  rh1,
  rh2,
  total,
  ((total + rec_host) / 2) AS total2
FROM
(
   SELECT host_name, 
       rec_host,
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + 
         rank_csb + rank_vfm + rank_orr
         ) / 6) AS total
   FROM lhr_reviews 
   GROUP BY host_name, rec_host
) t 
ORDER BY total 
DESC LIMIT 0,10;

edited Dec 11, 2012 at 8:47

answered Dec 11, 2012 at 8:25

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sashi Kant Over a year ago

+1 for the speed, I was thinking the same thing....:-) But in the subquery, please remove the ((total + rec_host) / 2) AS total2

Justin Over a year ago

I'm confused. You're selecting Aliases? That seems weird to me. although I'm a newbie at MySQL.

Mahmoud Gamal Over a year ago

@Justin - Yes these aliases are coming from the inner subquery, and it is OK. Try it.

bonCodigo Over a year ago

MahmoudGamal I guess you missed out rec_host in your subquery and what @Sashi Kant mentioned ;)

Mahmoud Gamal Over a year ago

@Justin - You are welcome any time, and Sorry for confusion .

Community · Accepted Answer · 2017-05-23 12:12:11Z

3

What you could do is this:

select x.*, ((x.total + rec_host) / 2) AS total2
from (

SELECT host_name, rec_host,
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) AS total
   FROM lhr_reviews 
   GROUP BY host_name
   ORDER BY total 
   DESC LIMIT 0,10
) as x
;

You cannot use the column as an alias when the alias and other column are in the same level of SELECT. So you can use a derived query which lets you basically rename your columns and/or name any computed columns.Check on Rubens Farias and Rob Van Dam answer here

PS: will search for a better article to update the answer :)

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Dec 11, 2012 at 8:30

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

2 Comments

Justin Over a year ago

Thank you, I learned a lot :) Sorry I couldn't give you the "checkmark" -- He beat you to it :)

bonCodigo Over a year ago

@MahmoudGamal healthy competition is very useful and we get better solutions with it :)

Collectives™ on Stack Overflow

Adding up Alias with other MySQL column value

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related