how many accounts that have never ordered? (in sql)

Question

the product_qty has the values that explains how many orders an account has made. The task here is to count the number of accounts who never ordered or purchased anything.

product_qty	account_id
4	1001
0	1002
5	1001
1	1003
0	1002
9	1001
0	1004

so far I've been able to write this:

SELECT 
DISTINCT(account_id),
CASE WHEN poster_qty = 0 THEN '0' ELSE 'not 0' END AS poster_qnty
FROM sqlchallenge1.orders 
GROUP BY 1, 2
ORDER BY 1

desired output:

product_qty	account_id_count
0	2
1	1
18	1

Answer 1

You need 2 levels of aggregation:

SELECT product_qty, 
       COUNT(*) account_id_count
FROM (
  SELECT account_id, 
         SUM(product_qty) product_qty
  FROM orders
  GROUP BY account_id
) t
GROUP BY product_qty
ORDER BY product_qty;

Or, with COUNT() window function if your version of MySql is 8.0+:

SELECT DISTINCT
       SUM(product_qty) product_qty,
       COUNT(*) OVER (PARTITION BY  SUM(product_qty)) account_id_count
FROM orders
GROUP BY account_id
ORDER BY product_qty;

See the demo.