SELECT 
        sector,
        CASE WHEn Sector LIKE 'Consumer%' THEN 'Consumer'ELSE 'other' END AS NewSector,
        COUNT(DISTINCT symbol) AS NrOfCompanies
    FROM SP500 
    GROUP BY sector 
    HAVING NrOfCompanies > 50 
    
    -- having is used for aggregated data while WHERE can be used before it for coluns as NewSector 
    -- which are calculated before GROUP BY