Double use of SUM() in DQ solution

Q&A
#1

Screen Link:

My Code: <!–’’’%%sql
WITH total_sales AS
(
SELECT
c.customer_id,
c.support_rep_id,
SUM(i.total) total
FROM invoice i
INNER JOIN customer c ON c.customer_id = i.customer_id
Group BY 1
)

SELECT 
    e.first_name ||' '||e.last_name employee_name,
    e.hire_date,
    ts.total total_sales
    
    FROM total_sales ts  
    INNER JOIN employee e on ts.support_rep_id = e.employee_id
    GROUP BY 1
    '''-->

**DQ solution** 

%%sql

WITH customer_support_rep_sales AS
    (
     SELECT
         i.customer_id,
         c.support_rep_id,
         SUM(i.total) total
     FROM invoice i
     INNER JOIN customer c ON i.customer_id = c.customer_id
     GROUP BY 1,2
    )

SELECT
    e.first_name || " " || e.last_name employee,
    e.hire_date,
    SUM(csrs.total) total_sales
FROM customer_support_rep_sales csrs
INNER JOIN employee e ON e.employee_id = csrs.support_rep_id
GROUP BY 1;

What I expected to happen: Why the SUM() is used twice (in the subquery and main query) in the DQ solution??

What actually happened: Using the SUM () only once in the subquery gives different values .

Replace this line with the output/error
#2

Hi @4jvarun,

Try to think about what happens during a GROUP BY operation. First you group by, one column, but then you need to specify which aggregation method you need on the others.

This is a helpful link to see the syntax.
https://www.techonthenet.com/sql/group_by.php

Hope this helps :slight_smile: