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 · Answer 1 · 2012-12-11T08:47:41.290

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 '12 at 08:47

answered Dec 11 '12 at 08:25

Mahmoud Gamal

78,257
17
139
164

1

+1 for the speed, I was thinking the same thing....:-) But in the subquery, please remove the `((total + rec_host) / 2) AS total2` – Sashi Kant Dec 11 '12 at 08:26
I'm confused. You're selecting Aliases? That seems weird to me. although I'm a newbie at MySQL. – Justin Dec 11 '12 at 08:28
1

@Justin - Yes these aliases are coming from the inner subquery, and it is OK. Try it. – Mahmoud Gamal Dec 11 '12 at 08:32
MahmoudGamal I guess you missed out `rec_host` in your subquery and what @Sashi Kant mentioned ;) – bonCodigo Dec 11 '12 at 08:43
1

@Justin - You are welcome any time, and Sorry for confusion . – Mahmoud Gamal Dec 11 '12 at 08:52

score 3 · Accepted Answer · edited May 23 '17 at 12:12

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 :)

Thank you, I learned a lot :) Sorry I couldn't give you the "checkmark" -- He beat you to it :) — Justin, Dec 11 '12 at 08:54
@MahmoudGamal healthy competition is very useful and we get better solutions with it :) — bonCodigo, Dec 11 '12 at 10:37

Adding up Alias with other MySQL column value

2 Answers2