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
– MatBailie, Commented Nov 7, 2020 at 9:31
Store dates as dates. And consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Nov 7, 2020 at 9:35
I repeate @Strawberry, because of this ('20200701','2020731','a02'). The last date is missing a '0'. — Luuk
– Luuk, Commented Nov 7, 2020 at 10:12

Luuk · Accepted Answer · 2020-11-07 10:32:12Z

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 7, 2020 at 10:32

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

answered Nov 7, 2020 at 9:31

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J.LOGAN Over a year ago

THANKS😄, If I have lots of data, like years, it will be more difficult.

Barmar Over a year ago

See stackoverflow.com/questions/75752/…

Luuk · Accepted Answer · 2020-11-07 10:18:36Z

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 7, 2020 at 10:18

answered Nov 7, 2020 at 9:35

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to get count value by one SQL statement?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related