Q:

How to use COUNT with a GROUP BY on two fields

I am trying to write a query where all buildings will show up for each date, and zeros are listed for the ones that do not exist. Below are an example table and data for my example.
CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(6) unsigned NOT NULL,
  `buildings` char(3) NOT NULL,
  `date` varchar(50) NOT NULL,
  `sold` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`id`, `buildings`, `date`, `sold`) VALUES
  ('1', '1', '2019-04-22 07:40:08','X'),
  ('2', '1', '2019-04-22 07:41:15',''),
  ('3', '1', '2019-04-22 07:42:10','X'),
  ('4', '3', '2019-04-22 07:43:50','X'),
  ('5', '1', '2019-04-22 07:44:27',''),
  ('6', '2', '2019-05-21 06:43:17','X'),
  ('7', '2', '2019-05-21 07:36:17',''),
  ('8', '1', '2019-05-21 06:32:22','X'),
  ('9', '3', '2019-05-21 07:43:50',''),
  ('10', '2', '2019-05-21 07:44:27','X');

And the SQL query that I am using as of now is listed below. The CONCAT and MID that is used to build the first column are getting the year and week of the year to create a 4 digit code per week.
SELECT CONCAT(MID(date,3,2),
           LPAD(WEEK(CONCAT(MID(date,1,4),'-',
           MID(date,6,2),'-',
           MID(date,10,2))),2,0)) AS YearWeek, 
       buildings, 
       COUNT(sold) AS count 
FROM table1 
WHERE sold = 'X' 
GROUP BY CONCAT(MID(date,3,2),
           LPAD(WEEK(CONCAT(MID(date,1,4),'-',
           MID(date,6,2),'-',
           MID(date,10,2))),2,0)),
         buildings

My issue with this is I get the data as below
YearWeek    buildings   count
1913        1           2
1913        3           1
1917        1           1
1917        2           2

When in fact I would like the data to look like the below so I can graph it.
YearWeek    buildings   count
1913        1           2
1913        2           0
1913        3           1
1917        1           1
1917        2           2
1917        3           0

To achieve this I have tried to do a left join to a subquery
(select distinct building as buildings from table1) but this is not working, and I think it is due to the fact that I am grouping by YearWeek and then building instead of the other way around.'
Any help on this query would be much appreciated!

A:

You can get the results you want by taking a CROSS JOIN of the distinct YearWeek and buildings values to get all the possible combinations of each, and then using a LEFT JOIN to the table to get the count of each type of buildings sold that week. For example:
SELECT yw.YearWeek,
       b.buildings,
       COALESCE(SUM(t.sold = 'X'), 0) AS count
FROM (SELECT DISTINCT buildings
      FROM table1) b
CROSS JOIN (SELECT DISTINCT RIGHT(YEARWEEK(date),4) AS YearWeek
            FROM table1) yw
LEFT JOIN table1 t ON t.buildings = b.buildings AND RIGHT(YEARWEEK(t.date),4) = yw.YearWeek
GROUP BY yw.YearWeek, b.buildings

Output:
YearWeek    buildings   count
1916        1           2
1916        2           0
1916        3           1
1920        1           1
1920        2           2
1920        3           0

Demo on dbfiddle
Note I have used MySQL's inbuilt YEARWEEK function to simplify the query, you can simply substitute your formula if that is not suitable.

