Alex Kubity Alex Kubity -4 years ago 54
MySQL Question

extend mysql query on one period to multuple periods

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?

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download