Using number reference of Column in GROUP BY and CASE statement- SQL

Hello, I have a question about using column’s number reference in SQL GROUP BY statement.

For example I have query as follows;

SELECT COUNT(*) AS Appearance,CASE

       WHEN url LIKE '%github.com%' THEN 'Github'

        WHEN url LIKE '%medium.com%' THEN 'Medium'

        WHEN url LIKE '%nytimes%' THEN 'NYtimes'

        ELSE 'others'

       END AS 'Source'
FROM hacker_news
GROUP BY 2;

Question : When using number reference on GROUP BY, does it take into account new column ‘Source’ as well that I created in CASE statement? or only columns in only original data scheme?

Hello @jiyeonjang7025175947, as far as I can see from the code you. have provided, Source and the column created by the CASE statement are the same thing. So in answer to your question, yes, it does group by the column created in the CASE.
I hope this helps!

1 Like

Hi!
Thank you for your reply.
But I think I am still little confused with using number reference in Group By :frowning:

In my query, this is how original table’s structure looks like - hacker_news

image

It has 5 columns title, user, score, timestamp, url - so I guess here ‘title’ must be 1st column with reference number 1.

I wonder what happened in my query, as I created new column basically called Apperance and Source.

If I use number reference in Group by, does number reference only take into my new columns in SELECT statement?(so it’s not like my two new columns are added as 6th and 7th reference) - that case reference 1 should be appearance and reference 2 should be source, right.

And I do not need to think about how my original table columns formed here as I am making new scheme of columns in my query ?

Hello @jiyeonjang7025175947, as far as I know, when you create a query using SELECT, the columns you reference are referring to the columns specified in the query:

SELECT COUNT(*) AS Appearance, CASE 
       WHEN url LIKE "%this%"
       END AS "Source"
FROM table_news
GROUP BY 2; /*This groups by 'source'-the second column referenced by your SELECT clause*/

I hope this helps!

1 Like

Thank you yes it helps!

1 Like