如何在连接两个表并将数量乘以行类别后，在psql中创建一列

Question

我创建了两个表并插入了值，没关系：

CREATE TABLE categories (
id numeric PRIMARY KEY,
name varchar
);
CREATE TABLE products (
id numeric PRIMARY KEY,
name varchar(50),
amount numeric,
price numeric(7,2),
id_categories numeric REFERENCES categories (id)
);
INSERT INTO categories (id, name)
VALUES 
(1,   'wood'),
(2,   'luxury'),
(3,   'vintage'),
(4,   'modern'),
(5,   'super luxury');

INSERT INTO products (id, name, amount, price, id_categories)
VALUES 
(1,   'Two-doors wardrobe', 100,  800,    1),
(2,   'Dining table', 1000,   560,    3),
(3,   'Towel holder', 10000,  25.50,  4),
(4,   'Computer desk',    350,    320.50, 2),
(5,   'Chair',    3000,   210.64, 4),
(6,   'Single bed',   750,    460,    1);

到目前为止，我必须显示一个基于类别重复的总和，如下所示：

我正在尝试它，但我一直面临着错误，例如在where中不允许使用聚合函数：

ALTER TABLE
categories 
ADD COLUMN 
sum INT;
SELECT 
categories.name, categories.sum
FROM 
categories 
JOIN
products 
ON categories.id = id_categories
WHERE 
categories.sum = products.amount *  COUNT(categories.name);

Answer 1

您必须将group-by-categories.name列添加到查询中。

SELECT 
categories.name, sum(products.amount) AS category_sum
FROM 
categories 
JOIN
products 
ON categories.id = id_categories
GROUP BY
categories.name

退货：

名称	category_sum
豪华	350
现代	13000
年份	1000
木材	850