# How do i count records only one time inside a time range?

I've table with record data.

I am attempting to count the records arranged with a version, however , the records have another criteria (ref) and really should counted only one time (per ref).

stats sample data as one example of the problem:

id stat_date  ref version
-------------------------
1  2012-01-25   1  A
2  2012-01-25   2  B
3  2012-01-25   3  A
4  2012-01-26   8  B
5  2012-01-26   2  B
6  2012-01-26   3  B  <-- version has been updated for ref=3

Simple counting would return

SELECT COUNT(*),version FROM stat GROUP BY version

1,A
5,B

The issue here's that just the last record with ref=3 (id=6) ought to be counted and (id=3) needs to be overlooked. So now you ask ,, how do i filter row (id=3) in the query?

I can not evaluate which I ought to place as symptom in the subquery

SELECT COUNT(*),version FROM stats
WHERE  stat_date BETWEEN "2012-01-25"  AND "2012-01-26"
AND id = (SELECT MAX(id) FROM stats WHERE <condition>)
GROUP BY 2

The expected result could be:

1,A (since id=3 is ignored)
3,B (since the first id=2 is ignored and only id=5 is taken into account)

It does not matter which row you ignore. All that's necessary would be to count ref once.

SELECT COUNT(DISTINCT ref),version FROM stat GROUP BY version

Edit:

Your example also needs to count the two rows where ref = 2 once too based on your logic.

This solutions satisfies the questions as posed even when it does not solve your unknown real problem

Try:

SELECT COUNT(*),version
FROM stats s1
WHERE stat_date BETWEEN "2012-01-25"  AND "2012-01-26" AND
not exists (SELECT null
FROM stats s2
WHERE s1.ref = s2.ref and s2.id>s1.id)
GROUP BY version

EDIT: Not certain to always work:

SELECT COUNT(*),version from
(select * from
(select * from stats
where stat_date BETWEEN "2012-01-25"  AND "2012-01-26"
order by ref, id desc) s0
group by ref) s1
group by version
SELECT COUNT(*),version FROM stats
WHERE  stat_date BETWEEN "2012-01-25"  AND "2012-01-26"
AND id = (SELECT MAX(s.id) FROM stats s WHERE s.id = id)
GROUP BY version
ORDER BY stat_date