MySQL: Count ids that only have only a specific action and how many days that action was taken-CodePudding

With a database of actions (starting at 1 and going up), I need to drop all rows of any parentid with a single action = 2, then take the remaining and count the number of actions happened on different days.

Here is my sample data:

id	datecre	action	parentid
1	2022-01-01 01:00:00	1	52
2	2022-01-02 01:00:00	1	52
3	2022-01-02 02:00:00	1	52
4	2022-01-03 01:00:00	1	65
5	2022-01-04 01:00:00	1	65
6	2022-01-05 01:00:00	1	65
7	2022-01-06 01:00:00	1	65
8	2022-01-07 01:00:00	1	72
9	2022-01-07 02:00:00	2	72

I have tried several queries:

WITH overpass(parentid, date) AS
(
    SELECT parentid, DATE(datecre) as date
    FROM good
    WHERE parentid NOT IN (
        SELECT DISTINCT parentid
        FROM good
        WHERE action=2)
        GROUP BY date
)
SELECT parentid, COUNT(date) as days
FROM overpass
GROUP BY date;

This outputs:

parentid	days
52	1
52	1
65	1
65	1
65	1
65	1

The desired output is:

parentid	days
52	2
65	4

I also tried these final queries with the CTE:

select parentid, sum(parentid) from overpass group by date;
select parentid, sum(date) from overpass group by date;
select parentid, count(date) from overpass;
select parentid, sum(parentid) from overpass;

CodePudding user response：

I think you have overcmplicated this problem as a combination of group by on parent id, counting distinct values of dates, while excluding action=2 will do fine without any CTEs:

select parentid, count(distinct date(datecre)) cnt
from good g2
where parentid not exists (select 1 from good g where g.action=2 and g.parentid=g2.parentid)
group by parentid