根据元素值计算分组平均值

Question

我有一个包含两列的表: Model和Price

Model       Price
Pilot       30
Civic       20
Highlander  10
Corolla     40
Rav4        25

我知道Pilot和Civic是本田汽车，而Highlander，Corolla，Rav4是丰田。我想计算一下，比如本田集团(30+20)/2和丰田(10+40+25)/3的平均价格。

输出表：

Mode    Average_price
Honda   25
Toyota  25

我的问题是不允许我在现有的表中创建新表或新列。

因此，我认为在查询中的某个地方，我需要列出所有条件

IF Model in ('Pilot','Civic') then Manufacturer='Honda' else if Model in ('Highlander','Corolla','Rav4') then Manufacturer='Toyota'
Then average by Manufacturer.

有人能帮我写一个查询吗？

谢谢,

哈里

Answer 1

您可以使用case表达式：

select 
    case 
        when model in ('Pilot', 'Civic') then 'Honda'
        when model in ('Highlander', 'Corolla', 'Rav4') then 'Toyota'
    end mode,
    avg(price) avg_price
from mytable
group by
    case 
        when model in ('Pilot', 'Civic') then 'Honda'
        when model in ('Highlander', 'Corolla', 'Rav4') then 'Toyota'
    end 

一些数据库通过允许在group by子句中使用别名(例如MySQL)来扩展SQL标准，因此您可以将其简化为：

group by mode

Answer 2

一个公用表表达式怎么样？

;WITH hondaCTE AS (
    SELECT 'Honda' AS [make]
    ,   SUM(ct.Price) / COUNT(1) AS [avg_price]
    FROM car_table AS ct
    WHERE ct.Model IN ('pilot','civic')
)
, toyotaCTE AS (
    SELECT 'Toyota' AS [make]
    ,   SUM(ct.Price) / COUNT(1) AS [avg_price]
    FROM car_table AS ct
    WHERE ct.Model NOT IN ('pilot','civic') -- Exclude Honda models
)
-- Union results
SELECT * FROM hondaCTE
UNION
SELECT * FROM toyotaCTE

输出：

make    avg_price
Honda   25
Toyota  25