CODEWITHSUNDEEP
mysqlsql
J.LOGAN
J.LOGAN

Reputation: 29

How to get count value by one SQL statement?

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？

Upvotes: 0

Views: 46

Answers (2)

Barmar
Barmar

Reputation: 781004

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

Upvotes: 1

Luuk
Luuk

Reputation: 14929

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;

Upvotes: 0

Related Questions