||date|type|
||2022-05-31|buy|
||2022-04-02|sell|
||2022-03-23|other|
||2022-02-26|other|
||2022-01-20|sell|
||2021-11-03|other|
||2021-10-14|buy|
||2021-09-28|buy|
||2021-09-17|other|
||2021-07-30|sell|
||2021-04-17|other|
||2021-03-01|other|
||2021-02-10|other|
||2021-01-18|sell|
||2020-12-14|buy|
||2020-12-11|other|
||2020-12-01|other|
||2020-09-28|sell|
||2020-09-14|other|
||2020-09-08|other|
hi
hope this helps
this is a microsoft sql server t-sql forum
what i did may not work in mysql .. you have to do the translation
create data script
drop table if exists #TempTable
create table #TempTable (dt date, type varchar(100) )
insert into #TempTable select '2022-05-31','buy'
insert into #TempTable select '2022-04-02','sell'
insert into #TempTable select '2022-03-23','other'
insert into #TempTable select '2022-02-26','other'
insert into #TempTable select '2022-01-20','sell'
insert into #TempTable select '2021-11-03','other'
insert into #TempTable select '2021-10-14','buy'
insert into #TempTable select '2021-09-28','buy'
insert into #TempTable select '2021-09-17','other'
insert into #TempTable select '2021-07-30','sell'
insert into #TempTable select '2021-04-17','other'
insert into #TempTable select '2021-03-01','other'
insert into #TempTable select '2021-02-10','other'
insert into #TempTable select '2021-01-18','sell'
insert into #TempTable select '2020-12-14','buy'
insert into #TempTable select '2020-12-11','other'
insert into #TempTable select '2020-12-01','other'
insert into #TempTable select '2020-09-28','sell'
insert into #TempTable select '2020-09-14','other'
insert into #TempTable select '2020-09-08','other'
; WITH cte AS
(
SELECT 'Q'
+ cast(datepart(QUARTER,dt) as varchar)
+ ''''
+ cast(datepart(year,dt) as varchar)
as period
, type
+ '='
+ cast(count(*) as varchar)
as events
FROM
#TempTable
GROUP BY
datepart(year,dt)
, datepart(QUARTER,dt)
, type
)
SELECT period, STRING_AGG(events,';') as events FROM cte GROUP BY period ORDER BY right(period,4), left(period,2)
