How to COUNT DISTINCT record SQL?

0
votes

I have a script to count distinct id for registration once per year

SELECT COUNT(DISTINCT id),
MONTH(reg_date), YEAR(reg_date)
FROM reg_tbl
WHERE YEAR(reg_date) IN (2013, 2014, 2015)

I want to have the data count each id only once per year even if it is returned more than once. I would also like the id to be counted in the maximum month it was registered. That is, if the id is registered in January, February and March of a specific year I would like it to be counted once but within the month of the latest registration.

2
sqlinformix

2 Answers

4
votes

First, you need to aggregate the data by year to get the latest value for each id in the year. Then, you can do the count by month:

SELECT COUNT(*), MONTH(reg_date), YEAR(reg_date)
FROM (SELECT id, YEAR(reg_date) as yyyy, MAX(reg_date) as reg_date
      FROM reg_tbl rt
      GROUP BY id, YEAR(reg_date)
     ) rty
WHERE yyyy IN (2013, 2014, 2015)
GROUP BY MONTH(reg_date), YEAR(reg_date);

Note: You no longer need count(distinct) in the outer query, because the subquery only returns one row per id per year.

0
votes

You can do it with a subquery

SELECT 
  COUNT(*) 
FROM
  (SELECT DISTINCT 
     id,
     MONTH(reg_date), 
     YEAR(reg_date)
   FROM 
     reg_tbl
   WHERE 
     YEAR(reg_date) IN (2013, 2014, 2015)) Data

Hope this helps