SQL Fetch multiple result counts from same set of data using same query

Question

I am working on product data, part of which has the below structure (let's call it product_serials):

The table is a collection of product serial numbers. The snapped field determines whether a specific product has been purchased or not via it's serial number. Am trying to query the table to get a count of both all serials and also all unpurchased serials of the same product_id, using a single SQL query. So far using COUNT(ps1.id) AND COUNT(ps2.id) ... WHERE ps2.snapped = FALSE does not seem to work, it still counts the same values for both all serials and unpurchased serials, and even exaggerates the count, so am definitely doing something wrong.

What could I be missing?

My SQL query as requested:

SELECT pd.id AS product_id, pd.description, 
COUNT(pds.id) AS total, COUNT(pds2.id) AS available 
FROM products pd 
LEFT JOIN product_serials pds ON pds.product_id = pd.id 
LEFT JOIN product_serials pds2 ON pds2.product_id = pd.id 
WHERE pds2.snapped = FALSE
GROUP BY pd.id 
ORDER BY pd.date_added DESC

Please provide a complete query you tried

Sianur
– Sianur

2018-07-05 08:53:24 +00:00
Commented Jul 5, 2018 at 8:53 — Sianur
– Sianur, Commented Jul 5, 2018 at 8:53

Sianur · Accepted Answer · 2018-07-05 11:29:07Z

1

Here you join tables (even multiplying them) and then apply a WHERE condition to both.

I suggest something like the following:

SELECT product_id, count(serial), count(unpurchased)
  FROM (SELECT product_id, serial,
      CASE WHEN snapped THEN NULL ELSE 1 END AS unpurchased)
  GROUP BY product_id

answered Jul 5, 2018 at 11:29

Sianur

6496 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sianur Over a year ago

It will select 1 for a not purchased product and Null for a purchased one. Count on this column will give you the count of 1s (i.e. not purchased).

Peter Over a year ago

Yap, this is just like using a derived table, worked for me. Thanks

Collectives™ on Stack Overflow

SQL Fetch multiple result counts from same set of data using same query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related