how to transfrom two separate queries into one long subselect-CodePudding

So I have this kind of problem. I have already the output what I wanted, but I want to get a better way to solve it. I want to calculate number of products sold with the number of attributes of this product.

First I have created a new table for statistics.

drop table if exists stat1 cascade;
create table stat1(
    id serial primary key,
    products integer,
    numberOfAtributtes integer
);

Here I am calculating products which are in the orders cart along with how manz attributes does this product have.
At the end I insert data to created table stat1.

with data as(
    (select k.product_id as pp, count( ap.atribut_id) as numberOfAtributes from productAtributes as ap
    JOIN cart k ON k.product_id = ap.product_id
    GROUP BY  k.product_id) )
insert into stat1(pp, numberOfAtributes)
select pp , numberOfAtributes from data;

Thats the output, which I am inserting into the table stat1.

And finally the third query which I am doing is counting how manz products have been sold with the same number of atributtes.

select count(numberOfAtributes), numberOfAtributes
from stat1
group by numberOfAtributes;

The given and (expected) output is:

Is it possible to shorten it into just one query(maybe using subselect? I tried but it didnt work), so without creating an additive table stat1?

CodePudding user response：

Using simple CTE instead of temporary table should work (not tested):

with stat1(products, numberOfAtributes) as (
    select k.product_id as pp, count( ap.atribut_id) as numberOfAtributes from productAtributes as ap
    JOIN cart k ON k.product_id = ap.product_id
    GROUP BY  k.product_id )
select count(numberOfAtributes), numberOfAtributes
from stat1
group by numberOfAtributes;