sql 计算以以下开头的行的频率

Question

让我们采用以下dataframe:

     Name
    ==================
    'Samsung Galaxy 3'
    'Samsung Galaxy 4'
    'Samsung Galaxy 4'
    'Samsung Galaxy 7'
    'ZTE'
    'ZTE'
    'Motorola'
    'Motorola'
    'Iphone'
    'Iphone'
    'Iphone'

出于某种原因，我想通过仅按 Iphone 和以 Samsung 开头的手机(无论型号)进行分组来计算。

我试过:

SELECT Name, count(Name)
FROM dataframe 
WHERE dataframe LIKE '%Samsung' OR dataframe LIKE '%Iphone'
GROUP BY Name

显然我明白了

  Name                count(Name)
 ==================|================
'Samsung Galaxy 3' |  1
'Samsung Galaxy 4' |  2
'Samsung Galaxy 7' |  1
'Iphone'           |  3

但我想得到的是将所有三星分组。输出应该看起来像

  Name                count(Name)
     ==================|================
    'Samsung Galaxy'   |  4
    'Iphone'           |  3

是否可以解决该查询？

Answer 1

我想你想要:

SELECT (CASE WHEN Name LIKE 'Samsung%' then 'Samsung'
             WHEN Name LIKE 'Iphone%' then 'Iphone'
             ELSE 'other'
        END) as which, COUNT(*)
FROM dataframe 
WHERE name LIKE 'Samsung%' OR name LIKE 'Iphone%'
GROUP BY which;