How to calculate aggregate function SUM on an alias column?

Question

SELECT a.question_id, 
       a.level, 
       Count(a.question_id) AS rank, 
       Sum(rank)        AS total 
FROM   logs AS a, 
       question AS b 
WHERE  a.question_id = b.q_id 
       AND a.level = '2' 
GROUP  BY a.question_id 
ORDER  BY rank DESC

SUM(COUNT(a.step_id)) AS total. Aliases are only available in GROUP BY, ORDER BY or HAVING (aside from direct output). — Brad Christie
– Brad Christie, Commented Jan 8, 2013 at 15:14
You are declaring GROUP BY a.question_id and also COUNT() and SUM() on the same field? — PinnyM
– PinnyM, Commented Jan 8, 2013 at 15:29

zessx · Accepted Answer · 2013-08-02 15:39:32Z

14

Simply wrap your reused alias with (SELECT alias) :

SELECT a.question_id, 
       a.level, 
       COUNT(a.question_id) AS rank, 
       SUM(SELECT(rank)) AS total 
FROM   logs AS a, 
       question AS b 
WHERE  a.question_id = b.q_id 
       AND a.level = '2' 
GROUP  BY a.question_id 
ORDER  BY rank DESC

answered Aug 2, 2013 at 15:39

zessx

68.9k29 gold badges139 silver badges166 bronze badges

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

3 Comments

Abhi Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT(...)) AS ... at line_1'

kunal Over a year ago

i am not sure why this answer is accepted and so many upvotes. its giving error syntax error

zessx Over a year ago

Maybe because this answer is almost 7 years old, and you may have a recent MySQL version.

Gordon Linoff · Accepted Answer · 2013-01-08 15:27:46Z

6

The scoping rules of SQL do not allow you to use an alias in the same select. Although this seems unreasonable, it is to prevent confusions such as:

select 2*x as x, x+1

Which x does the second variable refer to?

You can solve this problem by using a subquery:

select t.*, Sum(rank) AS total 
from (SELECT a.question_id, a.level, Count(a.question_id) AS rank, 
      FROM logs AS a join
           question AS b 
           on a.question_id = b.q_id 
      WHERE a.level = '2' 
      GROUP  BY a.question_id 
     ) t
ORDER BY rank DESC

I also fixed your join syntax. The use of a comma to mean a cross join with restrictions in the where clause is rather outdated.

answered Jan 8, 2013 at 15:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

PinnyM · Accepted Answer · 2013-01-08 15:25:28Z

0

It doesn't really make sense to do this unless you have a different grouping for SUM(rank) than you would for COUNT(a.question_id). Otherwise, the SUM will always be working on one row - which is the value of the result of COUNT. Moreover, you are asking for COUNT(a.question_id) where you have specified the GROUP BY clause to also use a.question_id. This is not going to return the results you are looking for.

If you clarify what your grouping would be for rank, a subquery could be made for this.

answered Jan 8, 2013 at 15:25

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Collectives™ on Stack Overflow

How to calculate aggregate function SUM on an alias column?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related