Mysql – Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products

countgroup byMySQL

I have table:

brands

brand_id  name
1         Apple
2         Sony
3         Nokia
4         HTC

products

id  brand_id qty
1   1        3
2   3        0
4   1        1
2   2        1

I need to get (all brands from brands table and products.qty > 0)

brand_id   count_products
1          2
2          1
3          0
4          0

I tried

SELECT `p`.`brand_id` AS `id`,
       COUNT(p.brand_id) AS COUNT
FROM `products` AS `p`
WHERE AND `p`.`qty` > 0
GROUP BY `p`.`brand_id`

But I get results only for brands that available in products_table.
Thanks for help.

Best Answer

SELECT b.brand_id,
       COUNT(p.brand_id)
FROM brands b
LEFT JOIN products p ON b.brand_id = p.brand_id
                    AND p.qty > 0
GROUP BY b.brand_id

Related Solutions

Mysql – How to get a list/result of best selling items

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

Since SUM(Quantity) is also included in the SELECT clause, you can reference its alias in ORDER BY instead of repeating the expression:

...
ORDER BY TotalQuantity DESC;

Sql-server – How to get a group where the count is zero

The reason your query did not work as intended:

Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not produce any entry for that.

Outer join (right or left) will give the result of inner join and in addition all non-qualifying records from the left or right table depending on the type (left or right) of outer join.

In this case, I put Street on the left of the join and used left outer join as you wanted all streets (even count is zero) in your result set.

Change your select query to this.

SELECT S.Name AS Street,
       Count(U.Username) AS COUNT
FROM Streets S
LEFT OUTER JOIN Users U ON U.Streetid = S.Id
GROUP BY S.Name

Result

Best Answer

Related Solutions

Mysql – How to get a list/result of best selling items

Sql-server – How to get a group where the count is zero

Related Question