Using name alias in GROUP BY clause gives error

Q&A DQ Courses
, ,
#1

Screen Link: https://app.dataquest.io/m/465/building-and-organizing-complex-queries/4/creating-views

My Code: <

CREATE VIEW chinook.customer_gt_90_dollars AS 
    SELECT
        c.*
    FROM chinook.customer c
    INNER JOIN chinook.invoice i ON i.customer_id = c.customer_id
    GROUP BY c.* HAVING SUM(i.total) > 90;

What I expected to happen:
While following the instructions to create a VIEW in this mission ( where the view should contain ALL columns from a table) I tried to use the name alias for the table in GROUP BY as my code shows above. But I get the following error. The answer code seems to use a shortcut i.e GROUP BY 1 . It makes me wonder why GROUP BY doesn’t like the table name alias followed by * and likes only the numbered shortcut in this case. I even tried using parenthesis around it like this : (c.*) but still get the error. Appreciate clarifying if it’s a syntax rule or something else I need be aware of. Thanks much.

What actually happened:

(sqlite3.OperationalError) near "*": syntax error
[SQL: CREATE VIEW chinook.customer_gt_90_dollars AS      SELECT         c.*     FROM chinook.customer c     INNER JOIN chinook.invoice i ON i.customer_id = c.customer_id     GROUP BY c.* HAVING SUM(i.total) > 90;]
#2

Hey, Durga. Nice try, unfortunately this doesn’t work.

The problem isn’t the alias, the problem is trying to group by all columns (I assume that’s your intent) by using the * wildcard.

If you wish to group by all columns, you need to name them explicitly, with or without an alias.

1 Like
#3

Thanks a lot!
I changed my code to GROUP BY c.customer_id instead and am able to verify output meets the goal ( all customers who spent total of > 90)

CREATE VIEW chinook.customer_gt_90_dollars AS 
    SELECT c.*, SUM(i.total) total_spent
    FROM chinook.customer c
    INNER JOIN chinook.invoice i ON i.customer_id = c.customer_id
    GROUP BY c.customer_id HAVING total_spent > 90;
     
SELECT * FROM chinook.customer_gt_90_dollars;
1 Like