SQLTeam.com | Weblogs | Forums

How to count duplicates and see the date created of the first


#1

I have no idea how to write a query to get this. Imagine a Table with records like this:

ContNum, Name, Semester, DateCreated
34, Mary , Spring, 3/1/ 2017
34, Mary, Fall, 10/1/2016
45, Jon, Winter, 1/5/2017
45, Jon, Spring , 3/15/17
45, Jon, Fall, 10/2/2016

I want to get back one line per unique ContactNum, with the count of how many there are for each ContactNum and the date of the first Date created. A result like this:

ContactNum, Name, Number of Semesters, Date of first Created
34, Mary, 2, 10/1/2016
45, Jon, 3, 10/2/2016

Is that even possible?
Appreciate ideas please!, Ultimately I would like a view with the result.
thank you,
Eva


#2

select ContNum, count(*) Counts, min(DateCreated) FirstDate from MyTable group by ContNum


#3

Oh my, that simple!!

But in the moment I add some more fields to show in the result, it does not work!.

Could it be put as a subquery or something so that I can select more fields, let's say fields from a Contact table by contNum ?


#4

Here is an example:

with contCounts
as (
select ContNum, count(*) Counts, min(DateCreated) FirstDate
from MyTable
group by ContNum
)
select *
from contCounts cc
join Contact c on c.ContNum = cc.ContNum