GROUP BY SHOW ALL DISTINCT COLUMNS Linking Two Tables

[iGuest 3]

GROUP BY SHOW ALL DISTINCT COLUMNS Linking Two Tables

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     | 4     |   1     || 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=0But 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);

-question by ajoe