通过sql中的其他值获取聚合值

Question

在它们的模式中，有两个表看起来如下所示：

create table PRODUCT_NOMENCLATURE(product_id integer, product_type varchar(100), product_name varchar(100));
insert into PRODUCT_NOMENCLATURE(product_id, product_type, product_name) values(490756, "MEUBLE", "Chaise");
insert into PRODUCT_NOMENCLATURE(product_id, product_type, product_name) values(389728, "DECO", "Boule de Noël");
insert into PRODUCT_NOMENCLATURE(product_id, product_type, product_name) values(549380, "MEUBLE", "Canapé");
insert into PRODUCT_NOMENCLATURE(product_id, product_type, product_name) values(293718, "DECO", "Mug");

CREATE table TRANSACTION(daate varchar(100), order_id integer, client_id integer, prop_id integer, prod_price integer, prod_qty integer);
insert into TRANSACTION values("01/01/20", 1234, 999, 490756, 50, 1);
insert into TRANSACTION values("01/01/20", 1234, 999, 389728, 3.56, 4);
insert into TRANSACTION values("01/01/20", 3456, 845, 490756, 50, 2);
insert into TRANSACTION values("01/01/20", 3456, 845, 549380, 300, 1);
insert into TRANSACTION values("01/01/20", 3456, 845, 293718, 10, 6);

我要找的是创建一个如下所示的表：

client_id.    meuble_sells.   deco_sells
999           50              14.24
845           400             60

我尝试过这样的查询：

select client_id, (select SUM(t.prod_price * t.prod_qty) from TRANSACTION t , PRODUCT_NOMENCLATURE p where t.prop_id=p.product_id group by t.client_id ) AS "ventes_meubles" from TRANSACTION;

但是，它给出了以下错误：Subquery returns more than 1 row

Answer 1

您可以使用条件聚合：

select client_id,
       sum(case when pn.product_type = 'MEUBLE' then prod_price * prod_qty end) as meuble_sells,
       sum(case when pn.product_type = 'DECO' then prod_price * prod_qty end) as deco_sells
from transactions t join
     PRODUCT_NOMENCLATURE pn
     on t.product_id = pn.prop_id
group by client_id