按返回进行分组，基于标题总共有三列

Question

我是SQL的新手。我正在尝试写一个查询，它将给我每个产品的总订单和每个产品的总支出。

我有下表：

order_id, title,price
1, spiderman 42 pc Lego, 18.42
2, superman 31 pc Lego, 14.37
3, spiderman 42 pc Lego, 18.42
4, spiderman 42 pc Lego, 14.23
5, superman 31 pc Lego, 14.37

我有这个查询，它完成了的一半工作

SELECT title, COUNT(*) as total_sales from orders
GROUP BY title
ORDER BY total_sales desc;

我想在查询中添加每个产品花费了多少钱(即每个产品的价格总和(，每个产品的成本不是恒定的。

预期输出：

title,total_sales, price_sum
spiderman 42 pc Lego, 3, 51.07
superman 31 pc Lego, 2, 28.74

Answer 1

SELECT title, COUNT(*) as total_sales,SUM(price) as price_sum from title
GROUP BY title
ORDER BY title;

只需添加总销售额

Answer 2

您可以创建一个view，它基本上是一个运行SQL语句的虚拟表(即使更新了orders表，视图表也会更新(

CREATE view orders_view AS
SELECT title, COUNT(*) as prod_sales from orders
GROUP BY title;
SELECT orders.title, prod_sales, SUM(price) from orders_view, orders
WHERE orders.title=orders_view.title
GROUP BY orders.title,prod_sales
ORDER BY prod_sales;

Answer 3

使用SUM聚合函数。

SELECT title, SUM(price) as price_sum from orders
GROUP BY title
ORDER BY title;