get aggregation values by other values in sql

I have two tables that looks like this in their schema :

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);

What I’m looking for is to create a table that looks like this :

client_id.    meuble_sells.   deco_sells
999           50              14.24
845           400             60

I have tried this query :

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;

but it gives me the following error saying Subquery returns more than 1 row

Answer

You can use conditional aggregation:

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

Advertisement

Answer