我正在尝试编写一个SQL报告,该报告将行,删除重复项和总结虚拟列中的值。
我有这个表
make | model | warranty | price
-------+--------+----------+-------
Honda | Accord | 2 | 700
Honda | Civic | 3 | 500
Lexus | ES 350 | 1 | 900
Lexus | ES 350 | 1 | 900
Lexus | ES 350 | 2 | 1300
Lexus | ES 350 | 3 | 1800
(6 rows)
我正在尝试创建一个报告,该报告添加了两个虚拟列, QTY 和 Total 。总计是 QTY *价格的总和。桌子应该喜欢下面的一个。
qty | make | model | warranty | price | total
-------+--------+--------+----------+-------------
1 | Honda | Accord | 2 | 700 | 700
1 | Honda | Civic | 3 | 500 | 500
2 | Lexus | ES 350 | 1 | 900 | 1800
1 | Lexus | ES 350 | 2 | 1300 | 1300
1 | Lexus | ES 350 | 3 | 1800 | 1800
(5 rows)
我认为这很简单:
select count(*) as qty, make, model, warranty,
avg(price) as price, sum(price) as total
from t
group by make, model, warranty;