GROUP BY shows all distinct columns

Question

mysql> select * from products;

+---------+-------------+-----------+------+
| prod_id | prod_source | prod_type | flag |
+---------+-------------+-----------+------+
|       1 | USA         |         2 |    0 |
|       2 | USA         |         2 |    0 |
|       3 | USA         |         2 |    0 |
|       4 | USA         |         3 |    1 |
|       5 | USA         |         3 |    0 |
|       6 | BRA         |         1 |    1 |
|       7 | BRA         |         2 |    1 |
+---------+-------------+-----------+------+

Required resulset:

+-----------+----------+
| prod_type | count(*) |
+-----------+----------+
|         2 |        3 |
|         3 |        0 |
|         4 |        0 |
+-----------+----------+


where condition
 prod_source = USA
 flag=0

but the resultset i'm getting is

+-----------+----------+
| prod_type | count(*) |
+-----------+----------+
|         2 |        3 |
+-----------+----------+

with the query

 SELECT prod_type ,count(*) 
   FROM products
   WHERE prod_source='USA'  and flag=0  
   GROUP by prod_type 
   ORDER by prod_type;

HOW do i get the resulset with count of all the prod_type's in USA ? It must display all the prod_type's in USA even if the count is 0 .

Table definition:

CREATE TABLE `products` (
  `prod_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `prod_source` VARCHAR(45) NOT NULL,
  `prod_type` INTEGER UNSIGNED NOT NULL,
  `flag` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`prod_id`)
)
ENGINE = InnoDB;

Data insertion:

INSERT INTO `products` VALUES (1,'USA',2,0),(2,'USA',2,0),(3,'USA',2,0),(4,'USA',3,1),(5,'USA',4,1),(6,'BRA',1,1),(7,'BRA',2,1);

Answer 1

Check your data, the query is correct but your data in the top table does not match the insert script (prod_id 5 is inserted with flag = 1)

TO get the 0s, you need to select the distinct prod_type then left join that onto your current query.

Answer 2

In SQL Server, I'd use the following:

SELECT prod_type,SUM(CASE WHEN flag=0 THEN 1 ELSE 0 END)
FROM products
   WHERE prod_source='USA' 
   GROUP by prod_type 
   ORDER by prod_type

I can't say whether the same would work in MySQL.

(Cheeky edit to correct column name in SELECT clause)