Prevent duplicate values using group by and count distinct simultaneously?

Question

I have a simple table with years and customer id and now I want to group by year and count distinct customers for each year. This is straightforward and works fine, my issue is that I don't want customers in year 1 to repeat in year 2, I only want to see new customers for each year. So how do I do that?

I have tried using count distinct with group by and even not in but it doesn't seem to work, it always gives me repeating values

select count (distinct customer ID), Year
FROM customers
group by year

lets say I have 100 customers for years 2015 to 2019 now I have

Year No of Customers
2015   30
2016   35
2017   40
2018   30
2019   10

Total 145 which is 45 more than 100 What I want is

Year  No of Customers
2015   30
2016   30
2017   20
2018   20
2019   10
Total  100

Please tag with the database you are using. – Gordon Linoff Oct 24 '19 at 22:38 — Gordon Linoff, Oct 24 '19 at 22:38
Yes its updated now its MS Sql server 2016 – learningtocode2k20 Oct 24 '19 at 22:48 — learningtocode2k20, Oct 24 '19 at 22:48

score 1 · Accepted Answer · answered Oct 24 '19 at 22:37

1

If you only want to count customers in the first year they appear, then use two levels of aggregation:

select min_year, count(*)
from (select customerid, min(year) as min_year
      from customers c
      group by customerid
     ) c
group by min_year
order by min_year;

To get the total, you can use grouping sets or rollup (not all databases support these. A typical method is:

select min_year, count(*)
from (select customerid, min(year) as min_year
      from customers c
      group by customerid
     ) c
group by min_year with rollup;

answered Oct 24 '19 at 22:37

Gordon Linoff

1,242,037
58
646
786

I will appreciate it a lot if you can explain to me, what is min(navrchar) do? is it built in function in sql? Also why are we grouping by customerid and not year? Finally I think am getting the total but value is null under column min_year. I did some basic testing and I think this works great, Thanks a ton. I will need to test it in different cases and soon I will mark this as the correct answer – learningtocode2k20 Oct 24 '19 at 22:57

score 0 · Answer 2 · answered Oct 24 '19 at 22:37

0

Perhaps something like this:

SELECT count (distinct c1.customerID), c1.Year 
FROM customers c1
WHERE c1.customerID not in (
    SELECT c2.customerID
    FROM customers c2
    WHERE c2.year < c1.year
)
GROUP BY year

answered Oct 24 '19 at 22:37

Brian Willis

22,768
9
46
50

Prevent duplicate values using group by and count distinct simultaneously?

2 Answers2