SQL Sum amount for column with unique values

Question

Update

Realised I was doing it correctly. The reason why I had the issue was because I didn't realise my data for Col1 wasn't as expected, having some Col1 that associates with multiple Col0 (It was supposed to be Col1:Col0 1:1 relationship. That's why the confusion of it's not working as intended.

Original Question

I'm using SQL query to sum a column for total revenue of distinct values in one of the columns, and return a table with combining with other attributes.

Here's my table:

Col 0    Col1   Col2(unique)  Revenue
X        1      A             10
X        1      B             20
X        1      C             0
X        2      D             5
X        2      E             8
Y        3      F             3
Y        3      G             0
Y        3      H             50

Desired output:

Col0   Col1   Revenue
X      1      30
X      2      13
Y      3      53

I tried:

WITH
  rev_calc AS (
    SELECT
      Col0,
      Col1,
      Col2, ##this is for further steps to combine other tables for mapping after this
      SUM(Revenue) AS total_revenue, ##total rev by Col1
    FROM table_input
    GROUP BY Col1, Col0, Col2 ##Have to group by Col0 and Col2 too as it raised error because of 'list expression'
    )
SELECT DISTINCT 
  table2.mappedOfCol0,
  rev_calc.Col1,
  rev_calc.Col2,
  rev_calc.total_revenue,
FROM another_table AS table2
LEFT JOIN rev_calc
  ON rev_calc.Col0 = table2.mappedOfCol0

But getting actual output with multiple rows of revenues under a specific Col1.

For example, when i filter by Col1 = 1 in the output table, I get a list of different revenue amount still:

Col1   total_revenue
1      10
1      20
1      0

I thought the GROUP BY should have sum up the revenue by distinctly under Col1. What did I miss out here? I also tried querying first FROM (SELECT DISTINCT Col1....) way but the sum(revenue) is producing a list of different revenue as well

Newbie to SQL here, appreciate if anyone can share any insights here. Thanks.

Does this answer your question? [Using group by on multiple columns](https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) — Hasan Fathi, Nov 22 '20 at 09:30
thanks Hasan, as mentioned in the question, I was trying to use GROUP BY, but my issue is that the output still gave me multiple revenue amount for Col1 even when I have queried with GROUP BY — unacorn, Nov 22 '20 at 09:55

score 2 · Answer 1 · answered Nov 22 '20 at 09:26

2

Don't you just want aggregation?

select col0, col1, sum(revenue) as revenue
from mytable
group by col0, col1

I don't understand what you are trying to do with col2 in the query. This produces the result you want for the data you showed, that contains a single table.

answered Nov 22 '20 at 09:26

GMB

216,147
25
84
135

thanks @GMB! So I still need col2 in the end result to be used when combining with another. table. I need to refer to that i.e. understand For each Col0, the list of values of Col1 & Col2 associated with it. Yes I want the aggregation, but i don't understand why my aggregation won't work in my query. If I don't include the Col2 in my query, I won't be able to select them to produce in my output table when combining at the 2nd part of the query – unacorn Nov 22 '20 at 09:51

score 1 · Accepted Answer · answered Nov 22 '20 at 10:19

1

As per explanation you provided, I think your requirement is aggregate revenue of selective records that map with another table based on Col2 values. If that is the case then you may try following query.

WITH
    rev_calc AS (
        SELECT 
            distinct(Col2) as Col2
        From table_input
        LEFT JOIN another_table
            ON another_table.Col2 = table_input.Col2
    )
SELECT
      Col0,
      Col1,
      SUM(Revenue) AS total_revenue 
FROM table_input 
WHERE Col2 in (select Col2 from rev_calc)
GROUP BY Col0, Col1;

answered Nov 22 '20 at 10:19

Nils

910
1
9
21

thanks for the suggestion Nilesh! I followed exactly and realised both yours and GMB way works. The reason why I had the issue was because I didn't realise my data for Col1 wasn't as expected, having some Col1 that associates with multiple Col0 (It was supposed to be Col1:Col0 1:1 relationship. That's why the confusion of it's not working as intended. Thanks! – unacorn Nov 22 '20 at 14:25

SQL Sum amount for column with unique values

2 Answers2