Menu
How to calculate aggregate function SUM on an alias column?
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
422
The SQL AGGREGATE SUM() function returns the SUM of all selected column. Applies to all values. Return the SUM of unique values.
Using the CALCULATED Keyword with Column AliasesThe CALCULATED keyword enables PROC SQL users to reference column aliases that are associated with calculated expressions. The column alias referenced by the CALCULATED keyword can be in the WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause.
Generally, aliases are used to make the column headings in your result set easier to read. Most commonly, you will alias a column when using an aggregate function such as MIN, MAX, AVG, SUM or COUNT in your query.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
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.
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
