What does GROUP BY 1 do in the query?

Screen Link:

My Code:

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

I don’t really understand why in the answer to the mission it is used GROUP BY 1 ?
Does this mean that the GROUP BY clause groups by the first column in the results table?

1 Like

It means you’re grouping by the first item in the SELECT as explained in step 6 of the Joining Data in SQL mission:

Previously, we’ve used column names when specifying order for our query results, like so:

SELECT name, migration_rate FROM FACTS
ORDER BY migration_rate desc;

There is a handy shortcut we can use in our queries which lets us skip the column names, and instead use the order in which the columns appear in the SELECT clause. In this instance, migration_rate is the second column in our SELECT clause so we can just use 2 instead of the column name:

SELECT name, migration_rate FROM FACTS
ORDER BY 2 desc;

You can use this shortcut in either the ORDER BY or GROUP BY clauses. Be mindful that you want to ensure your queries are still readable, so typing the full column name may be better for more complex queries.

1 Like

I might not understand the use of “GROUP BY” with " * " mark.
When i use * mark that means i want to shoose all the columns. And then i use GROUP BY 1 which means I want to group by the first column from all the columns?
The “*” mark and GROUP BY clause misleads me.

1 Like

Yes, that’s exactly it.

Is it good use ‘GROUP BY’ for one column if we select more than one?
I read the post Unnecessary additional GROUP BY in 190-3? in which Bruno explained that when select more than one column its better to use GROUP BY clause for evere columns. Otherwise mistakes in countings can occurs.

Could @Bruno explain the difference between the two scenarios? I’m not sure I get it completely.