CODEWITHSUNDEEP
sqlcount
Potocpe1
Potocpe1

Reputation: 35

All combinations of counts

I have a table, with columns product_id, status, for example:

product_id status
1 ok
2 bad
1 ok
3 bad
2 bad
1 ok

I'd like to show count of all possible combinations of product_ID and status:

product_id status count
1 ok 3
1 bad 0
2 ok 0
2 bad 2
3 ok 0
3 bad 1

The solution I've found is that I could use a Cartesian join and then union it with regular counts and aggregate the results (works fine):

SELECT product_id, status, SUM(cnt) FROM (
---all combinations, no count
    SELECT DISTINCT t1.product_id, t2.status, 0 AS cnt
    FROM 
    details t1,
    details t2

    UNION

---counts of existing combinations
    SELECT DISTINCT product_id, status, COUNT(status) AS cnt
    FROM details
    GROUP BY product_id, status) AS T1
GROUP BY product_id, status

Now I am wondering, is here a better way to do it?

I learning SQL with PostgreSQL and Access SQL. Comments are added to clarify (left-out in Access code).

Upvotes: 0

Views: 39

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272156

Use CROSS JOIN to build all combinations and top up with a LEFT JOIN:

SELECT p.product_id, s.status, COUNT(t.any_not_null_column)
FROM (SELECT DISTINCT product_id FROM t) AS p
CROSS JOIN (SELECT DISTINCT status FROM t) AS s
LEFT JOIN t ON p.product_id = t.product_id AND s.status = t.status
GROUP BY p.product_id, s.status

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The following is a Postgres solution (a database I strongly recommend over MS Access). The idea is to generate all the rows and then use left join and group by to get the counts you want:

select p.product_id, s.status, count(d.product_id)
from (select distinct product_id from details) p cross join
     (values ('ok'), ('bad')) s left join
     details d
     on d.product_id = p.product_id and d.status = s.status
group by p.product_id, s.status;

Note: You might have other tables that have the list of products and/or statuses that you want.

An equivalent version in MS Access (which would also work in Postgres) might look like:

select p.product_id, s.status, count(d.product_id)
from ((select distinct product_id from details) p, 
      (select distinct status from details) s
     ) left join
     details d
     on d.product_id = p.product_id and
        d.status = s.status
group by p.product_id, s.status;

Upvotes: 0

Related Questions