Building and Organizing Complex Queries (7/10)

Q&A
,
#1

Screen Link:
https://app.dataquest.io/m/373/building-and-organizing-complex-queries/7/multiple-named-subqueries

My Code: 

WITH 
c_india AS
(
SELECT *
FROM customer
WHERE country = "India"
),
c_total AS
(
SELECT
customer_id,
SUM(total) total 
FROM invoice
GROUP BY 1
)

SELECT
ci.first_name || " " || ci.last_name customer_name,
SUM(ct.total) total_purchases
FROM c_india ci
INNER JOIN c_total  ct ON ci.customer_id = ct.customer_id
ORDER BY 1

What I expected to happen: I expected to see two rows returned

What actually happened: Only one row is returning

customer_name      total_purchases
Puja Srivastava      183.14999999999998

There are fewer rows than there should be
1 Like
#2

Hello @thomas.palesky

This modification of your query worked.

The problem was with the SUM in your main query.

WITH 
c_india AS
(
SELECT *
FROM customer
WHERE country = "India"
),
c_total AS
(
SELECT
customer_id,
SUM(total) total 
FROM invoice
GROUP BY 1
)
SELECT
ci.first_name || " " || ci.last_name customer_name,
ct.total total_purchases
FROM c_india ci
INNER JOIN c_total  ct ON ci.customer_id = ct.customer_id
ORDER BY 1

Please see how I solved it:

WITH
    from_india AS (
        SELECT * FROM customer
        WHERE country LIKE '%India%'),
        
   total_sum AS (
       SELECT 
             fi.first_name || ' ' || fi.last_name customer_name,
             SUM(inv.total) total_purchases
       FROM from_india fi
       INNER JOIN invoice inv ON inv.customer_id = fi.customer_id
       GROUP BY 1
       ORDER BY 1)
SELECT * FROM total_sum;
2 Likes
#3

Thank you! I feel like Iā€™m close, but not close at all haha!

1 Like