extend mysql query on one period to multuple periods

Question

I have query that counts the number of distinct values of a given field for a given week that where not recorder before. It counts the number of new values for the week.

SELECT COUNT(DISTINCT (field)) FROM table WHERE (creation BETWEEN CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) + 5 DAY AND CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) - 2 DAY) AND field NOT IN (SELECT DISTINCT (field) FROM table WHERE creation < CURDATE() - INTERVAL DAYOFWEEK(CURDATE()) + 5 DAY);

I need to extend the query to be able to do the same computation for all the weeks in the past two months.

How can I do it?

Thorsten Kettner · Accepted Answer · 2017-06-14 09:59:27Z

Well, if I understand this correctly, a field occurs in some week for the first time. You want to count per week how many such first occurrences exist. So get the first date per field first. Then count per week.

select yearweek(first_occurence), count(*) from ( select field, min(creation) as first_occurence from table group by field ) fields group by yearweek(first_occurence) order by yearweek(first_occurence);

Collectives™ on Stack Overflow

extend mysql query on one period to multuple periods

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related