How to compute the sum over other sums in Snowflake-CodePudding

How can I compute the sum over other sums in Snowflake ? For example on this database:

id    Q1    Q2   Q3   Q4
1      1     2    3    4   
2      0     1    2    3   
3      3     2    1    0

I would like to obtain:

sumQ1   sumQ2   sumQ3   sumQ4  Total

 4       5      6       7       22

CodePudding user response：

SELECT 
    SUM(Q1) AS sumQ1
  , SUM(Q2) AS sumQ2 
  , SUM(Q3) AS sumQ3 
  , SUM(Q4) AS sumQ4
  , SumQ1   SumQ2   SumQ3   SumQ4 AS Total 
FROM table

I haven't tested it, but I think that should do the trick.

CodePudding user response：

try this

SELECT sum(q1_sum) as q1, SUM(q2_sum) as q2, SUM(q3_sum) as q3, SUM(q4_sum) As q4 , (q1  q2 q3 q4) total FROM 
(
  SELECT id, SUM(Q1) as q1_sum, SUM(Q2) as q2_sum, SUM(Q3) as q3_sum, SUM(Q4) as q4_sum FROM 
  (
  SELECT 1 as id, 1 as Q1 , 1 as q2, 2 as q3 , 1 q4 UNION ALL
  SELECT 1 as id, 1 as Q1 , 1 as q2, 1 as q3 , 3 q4 UNION ALL
  SELECT 2 as id, 1 as Q1 , 1 as q2, 1 as q3 , 1 q4 UNION ALL
  SELECT 2 as id, -1 as Q1 , 0 as q2, 1 as q3 , 2 q4 UNION ALL
  SELECT 3 as id, 2 as Q1 , 1 as q2, 1 as q3 , 1 q4 UNION ALL
  SELECT 3 as id, 1 as Q1 , 1 as q2, 0 as q3 , 2 q4
  ) GROUP BY id
)  ;