sql - 汇总数据的分组依据

Question

尝试在这里做一些事情来创建一种摘要数据。我不确定这是否是最优雅的 sql 代码!

我有下表

Product             Channel         Sold
-------------------     ----------------------  
PC                  Web             48
Laptop              Web             2
Speakers            Web             74
DVDs                Web             33
PC                  Shop            1
Laptop              Shop            1
Speakers            Shop            1
DVDs                Shop            5
PC                  Door-to-door    7
Laptop              Door-to-door    16
Speakers            Door-to-door    9
DVDs                Door-to-door    21
PC                  Referals        7
Laptop              Referals        16
Speakers            Referals        9
DVDs                Referals        21

我想查询数据，以便获得代表“直接”销售的数据，即网络销售和商店销售的总和，从而忽略上门销售和推荐。

Product             Channel         Sold
-------------------     ----------------------  
PC              Direct          49
Laptop          Direct          3
Speakers        Direct          75
DVDs            Direct          38

有谁知道我该怎么做？我正在考虑 Group by (select .... group by) 但我尝试的一切都是绝望的失败!哈哈。

提前致谢。

DS

编辑!

如果我想将上门推销和推荐放在一起作为“次要”怎么办？这容易实现吗？所以我正在寻找...

Product             Channel         Sold
-------------------     ----------------------  
PC              Direct          49
Laptop          Direct          3
Speakers        Direct          75
DVDs            Direct          38
PC              Secondary       14
Laptop          Secondary       32
Speakers        Secondary       18
DVDs            Secondary       42

再次感谢!

DS

Answer 1

您只需按 channel 过滤出记录，并使用Sold列上的SUM()对每个组(特别是产品。

SELECT  Product,    
        'Direct' Channel,
        SUM(Sold) TOtalSold
FROM    TableName
WHERE   Channel IN ('Web','Shop')
GROUP   BY Product

• SQLFiddle Demo

更新

SELECT  Product,    
        CASE    WHEN Channel IN ('Web','Shop') 
                THEN 'Direct'
                ELSE 'Secondary'
        END Channel,
        SUM(Sold) TOtalSold
FROM    TableName
GROUP   BY Product,
        CASE    WHEN Channel IN ('Web','Shop') 
                THEN 'Direct'
                ELSE 'Secondary'
        END 
ORDER   BY Channel

• SQLFiddle Demo

输出

╔══════════╦═══════════╦═══════════╗
║ PRODUCT  ║  CHANNEL  ║ TOTALSOLD ║
╠══════════╬═══════════╬═══════════╣
║ Laptop   ║ Direct    ║         3 ║
║ Speakers ║ Direct    ║        75 ║
║ DVDs     ║ Direct    ║        38 ║
║ PC       ║ Direct    ║        49 ║
║ Laptop   ║ Secondary ║        32 ║
║ Speakers ║ Secondary ║        18 ║
║ DVDs     ║ Secondary ║        42 ║
║ PC       ║ Secondary ║        14 ║
╚══════════╩═══════════╩═══════════╝