I have resutl sql with syntax:

SELECT AreaName, Date, Total
FROM table
GROUP BY AreaName, Date

Here Result:

But I want include AreaName if date data not exist with value Total 0. Like here:

Any Advice ?

CodePudding user response：

We can use sub-queries with DISTINCT to get lists of all dates and all AreaNames and then cross join them.
Warning: this multiplies the values and could quickly create an enormous result set containing a host of zero values.

create table Areas(
AreaName varchar(25),
AreaDate Date,
Total int);
insert into Areas values
('London', '2021-01-01',  2),
('Paris',  '2021-01-01',  3),
('London', '2021-02-01',  4),
('Beijing','2021-02-01',  5);

select
  a.AreaName,
  b.AreaDate,
  coalesce(c.Total,0) "Total"
from
(select distinct AreaName from Areas) a
cross join 
(select distinct AreaDate from Areas) b
left join Areas c
on a.AreaName = c.AreaName
and b.AreaDate = c.AreaDate
order by AreaDate, AreaName

AreaName | AreaDate   | Total
:------- | :--------- | ----:
Beijing  | 2021-01-01 |     0
London   | 2021-01-01 |     2
Paris    | 2021-01-01 |     3
Beijing  | 2021-02-01 |     5
London   | 2021-02-01 |     4
Paris    | 2021-02-01 |     0

db<>fiddle here

CodePudding user response：

WITH
areas AS ( SELECT DISTINCT AreaName 
           FROM source_table ),
dates AS ( SELECT DISTINCT `Date`
           FROM source_table )
SELECT AreaName, `Date`, COALESCE(Total, 0) Total
FROM areas
CROSS JOIN dates
NATURAL LEFT JOIN source_table;