CODEWITHSUNDEEP
mysqlsqlgroup-by
MBasith
MBasith

Reputation: 1499

SQL GROUP BY COUNT and Include 0 Values

I have the below simple table where I am using group by to get a count of fruit type by country. However, I would like to include 0 values where the country did not have any quantity of the fruit type. The current query I have excludes the 0 values where country does not have that fruit type. How include it so that the country that does not have that fruit type is displayed with a 0 value as show in my desired output?

Including Sample Table SQL:

CREATE TABLE `TABLE1` (
  `ID` INT NOT NULL,
  `COUNTRY` VARCHAR(45) NULL,
  `FRUIT` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`));
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('1', 'Mexico', 'Mango');
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('2', 'Brazil', 'Mango');
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('3', 'Brazil', 'Orange');
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('4', 'Brazil', 'Orange');
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('5', 'Mexico', 'Apple');
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('6', 'Brazil', 'Grape');
INSERT INTO `TABLE1` (`ID`, `COUNTRY`, `FRUIT`) VALUES ('7', 'Brazil', 'Pear');

Sample Table:

Id  Country  Fruit
------------------
1   Mexico   Mango
2   Brazil   Mango
3   Brazil   Orange
4   Brazil   Orange
5   Mexico   Apple
6   Brazil   Grape
7   Brazil   Pear

Group By:

SELECT COUNTRY, FRUIT, COUNT(COUNTRY) as NUM FROM TABLE1
GROUP BY FRUIT, COUNTRY
ORDER BY COUNTRY

Current Output:

    Country Fruit   Count    
    Brazil  Mango   1
    Brazil  Grape   1
    Brazil  Orange  2
    Brazil  Pear    1
    Mexico  Mango   1
    Mexico  Apple   1

Desired Output:

    Country Fruit   Count  
    Brazil  Mango   1
    Brazil  Mango   0
    Brazil  Grape   1
    Mexico  Grape   0
    Brazil  Orange  2
    Mexico  Orange  0
    Brazil  Pear    1
    Mexico  Pear    0
    Mexico  Mango   1
    Brazil  Mango   0
    Mexico  Apple   1
    Brazil  Apple   0

Upvotes: 1

Views: 127

Answers (2)

Nick
Nick

Reputation: 147146

You need to find all the distinct values of Country and Fruit and CROSS JOIN them; this can then be LEFT JOINed to your table to get the counts for each Country/Fruit combination:

SELECT c.Country,
       f.Fruit,
       COUNT(t.Fruit) AS Count
FROM (
  SELECT DISTINCT Country FROM table1
) c
CROSS JOIN (
  SELECT DISTINCT Fruit FROM table1
) f
LEFT JOIN table1 t ON t.Country = c.Country and t.Fruit = f.Fruit
GROUP BY c.Country, f.Fruit

Output:

Country     Fruit   Count
Brazil      Apple   0
Brazil      Grape   1
Brazil      Mango   1
Brazil      Orange  2
Brazil      Pear    1
Mexico      Apple   1
Mexico      Grape   0
Mexico      Mango   1
Mexico      Orange  0
Mexico      Pear    0

Demo on db-fiddle

Upvotes: 2

George Joseph
George Joseph

Reputation: 5922

Here is a way to do this.

Cartesian product the fruits with the countries. This will get you all country-fruit combinations. Once you get this perform a left join with your table and perform the group by.

with all_fruits
  as (select distinct fruit
        from table1
     )    
    ,all_countries  
  as(select distinct country
        from table1
     )
    ,fruit_country
   as(select country,fruit
        from all_fruits
        join all_countries
     )
    select a.country
          ,a.fruit
          ,count(b.fruit) as count
     from fruit_country a
left join table1 b
       on a.country=b.country
      and a.fruit=b.fruit 
 group by a.country
         ,a.fruit

Upvotes: 1

Related Questions