Ytukyg

I am learning SQL now and I am kind of confused when encountering a query like this:

SELECT CASE 

        WHEN url like '%abc.com%'THEN 'abc'

      When url like '%def.com%'THEN 'def'

      When url like '%smiley.com%' THEN 'Smiley'

      Else 'others'

      End AS 'sites', count(*)

    FROM websites_sources

    GROUP BY 1;

The query above works correctly, but only if the GROUP BY Statement uses 1. If I write Group BY 'sites', the result only shows 'others' and the total count of ALL rows (instead of the count for 'others' only and other sites).

My question is, so for GROUP BY Statement we can only use number to indicate grouping, and not the name of the column?

Thanks for your help!

Only use single quote for string and date constants. Never use single quotes for column aliases. You'll just confuse yourself.

Try this:

SELECT (CASE WHEN url like '%abc.com%' THEN 'abc'

             WHEN url like '%def.com%' THEN 'def'

             WHEN url like '%smiley.com%' THEN 'Smiley'

             ELSE 'others'

         END) AS sites,

        count(*)

FROM websites_sources

GROUP BY sites;

You are grouping by the string 'sites', which is a constant, not the column named sites.