How to get count value by one SQL statement?

Question

I have a source data like：

CREATE TABLE `test` (
  `startdate` varchar(100) DEFAULT NULL,
  `stopdate` varchar(100) DEFAULT NULL,
  `code` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test (startdate,stopdate,code) VALUES 
('20200630','20200731','a01')
,('20200701','2020731','a02')
,('20200702','20200801','a03')
,('20200901','20201001','a04')
,('20200629','20200701','a05')
,('20200621','20200628','a06')
;

I need to get data for every day between 20200701 and 20200703:

select '0701' as a,count(*) as b from test where startdate <= 20200701 and stopdate >= 20200701
union
select '0702' as a,count(*) as b from test where startdate <= 20200702 and stopdate >= 20200702
union
select '0703' as a,count(*) as b from test where startdate <= 20200703 and stopdate >= 20200703

But the problem is I actually have lots of data, I can not use this union one by one. How to optimize this statement？

Create a calendar table. They're insanely useful; just a table of all dates you'll ever need, with the ability to include columns like whether they're a public holiday. Then you can join on that table (with a predicate like your where clause) and group by that calendar table's date column. — MatBailie, Nov 07 '20 at 09:31
Store dates as dates. And consider handling issues of data display in application code — Strawberry, Nov 07 '20 at 09:35
I repeate @Strawberry, because of this `('20200701','2020731','a02')`. The last date is missing a '0'. — Luuk, Nov 07 '20 at 10:12

score 1 · Answer 1 · edited Nov 07 '20 at 10:32

1

Join with a synthesized table that lists all the dates you want to compare with.

SELECT RIGHT(x.date,4) AS a, COUNT(*) AS b
FROM test
JOIN (
    SELECT '20200701' AS date
    UNION
    SELECT '20200702' AS date
    UNION
    SELECT '20200703' AS date
) AS x ON x.date BETWEEN test.startdate AND test.stopdate
GROUP BY x.date

edited Nov 07 '20 at 10:32

Luuk

12,245
5
22
33

answered Nov 07 '20 at 09:31

Barmar

741,623
53
500
612

THANKS, If I have lots of data, like years, it will be more difficult. – J.LOGAN Nov 12 '20 at 08:42
See https://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe – Barmar Nov 12 '20 at 08:47

Luuk · Answer 2 · 2020-11-07T10:18:36.377

0

A bit clumsy because working with varchars that contain a data, but:

with recursive sel as (
  select CONVERT('20200701',CHAR(20)) as d 
  union all 
  select date_format(adddate(d,interval 1 day),'%Y%m%d') 
  from sel 
  where d< '20200703') 
select d, count(*)
from sel
left join test on startdate <= d and stopdate >=d
group by d;

edited Nov 07 '20 at 10:18

answered Nov 07 '20 at 09:35

Luuk

12,245
5
22
33

How to get count value by one SQL statement?

2 Answers2