mysql - 选择一个项目，该项目在数据库中重复出现的总次数以及该项目状态为1的次数

Question

我的产品表中有一个产品，其状态为已损坏或未损坏。

我需要一个 sql 查询来显示Productid，该项目在表中的总数，乘以其状态为一，

e-g  product_id  |  tottal_no_of_products_oF_this_type  | broketn
        5                   10                          |   3

SELECT   product_id,
         COUNT(product_id) AS duplicate_column, product_status
FROM     products_stored where product_status=broken
GROUP BY product_id
ORDER BY COUNT(product_id) DESC;

Answer 1

你很接近。您可以使用条件聚合来获取损坏的部分。例如:

select
  product_id,
  count(*) as total_no_of_products_this_type,
  sum(case when product_status = 'broken' then 1 else 0 end as broken
from products_stored
group by product_id
order by count(*) desc