1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

How to perform aggregation (sum) on a computed column?

Discussion in 'SQL - Questions and Answers' started by spartacus, Nov 30, 2006.

  1. spartacus Guest

    Dialect: SQL 92
    How can I make this query work?

    SELECT (AGE * 10) AS XYZ, SUM(XYZ) FROM MY_TABLE
    spartacus, Nov 30, 2006
    #1

  2. ben Administrator

    Dialect: SQL 92
    There are several ways you could try this. The easisest however (but probably not the most efficient) is to use a subquery.

    SQL:

    
SELECT 
    
(AGE * 10) AS XYZ, 
    
(SELECT SUM(AGE * 10) FROM MY_TABLE ) as XYZ_SUM
    
FROM MY_TABLE
    ben, Dec 1, 2006
    #2

  3. ben Administrator

    Dialect: MySQL
    This is specific to MySQL. This will give you a running sum. The last row will have the total sum of all the ages.

    SQL:

    
SET @a := 0;
    
SELECT 
    
(AGE * 10) AS XYZ, 
    
@a := @a + (AGE*10)
    
 FROM MY_TABLE
    ben, Dec 1, 2006
    #3

  4. Dimitar Guest

    Not answering the general question, merely a solution to your concrete example:
    SQL:

    
select age*10 as xyz,t.* from my_table join (select sum(age) * 10 from my_table) t

    Notes:
    1) The sum is computed just once and returned as a table of cardinality 1. The table is then joined with my_table. The whole statement requires two full table scans on my-table.
    2) (A x 10) + (B x 10) = (A + B) x 10
    Dimitar, Jan 23, 2007
    #4

Share This Page