Sum Column Results in SQL

Question

How do you sum the results of a calculated column into one number in SQL?

SELECT
    id, SUM(cost + r_cost) AS Revenue 
FROM
    revenue_table 
WHERE
    signup_date >= '2015-01-01' 
GROUP BY 
    id 
ORDER BY 
    Revenue DESC 
LIMIT 20;

This query displays the revenue to date of the top 20 customers. How can I quickly do a total sum of the Revenue to get the total Revenue of the top 20 guys?

Two options: you could use Group By id With Rollup, or you could wrap everything in a sub-query and SUM the Revenue column. — Siyual
– Siyual, Commented Mar 2, 2015 at 18:16

Barranka · Accepted Answer · 2015-03-02 18:23:34Z

1

Assuming you're using MySQL:

-- Option 1: Simply put your query in the FROM clause and sum the result
select sum(Revenue)
from (select id, sum(cost + r_cost) as Revenue
      from revenue_table
      where signup_date >= '2015-01-01'
      group by id
      order by Revenue desc
      limit 20) as a

-- Option 2: Use, as suggested by Siyual in his comment, ROLLUP.
--           You'll have to use a subquery too, because 
--           LIMIT is applied after the ROLLUP
select id, sum(a.Revenue) as Revenue
from (select id, sum(cost + r_cost) as Revenue
      from revenue_table
      where signup_date >= '2015-01-01'
      group by id
      order by Revenue desc
      limit 20) as a
GROUP BY id WITH ROLLUP

answered Mar 2, 2015 at 18:23

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

Sum Column Results in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related