Prev: Query total fish by angler by week
Next: For XML Path / used with Pivot
From: DavidC on 30 Jul 2010 17:51
I have a view that I want to count the occurrance of a FedIDNo only once in a
group of linked SSNs (using ROW_NUMBER() OVER ?). For instance, if I have
the following I want to count only 1:

FedIDNo SSN

123456789 987654321
123456789 998877654

Below is my SQL.

SELECT dbo.ClientInfo.FedIDNo, dbo.People.SSN
FROM dbo.Timesheets INNER JOIN
dbo.ClientInfo ON dbo.Timesheets.ClientLinkID =
dbo.ClientInfo.PeopleLinkID INNER JOIN
dbo.People INNER JOIN
dbo.PeopleLink ON dbo.People.PersonID =
dbo.PeopleLink.PersonID ON dbo.Timesheets.WorkerLinkID =
dbo.PeopleLink.PeopleLinkID
GROUP BY dbo.ClientInfo.FedIDNo, dbo.People.SSN


Thanks.

--
David
From: Eric Isaacs on 30 Jul 2010 19:33
Assuming you don't want it as part of your result set (which wouldn't
make sense because you have the SSN in there) you just need to use
COUNT(DISTINCT fieldname)...

SELECT COUNT(DISTINCT dbo.ClientInfo.FedIDNo)
FROM dbo.Timesheets INNER JOIN
dbo.ClientInfo ON dbo.Timesheets.ClientLinkID =
dbo.ClientInfo.PeopleLinkID INNER JOIN
dbo.People INNER JOIN
dbo.PeopleLink ON dbo.People.PersonID =
dbo.PeopleLink.PersonID ON dbo.Timesheets.WorkerLinkID =
dbo.PeopleLink.PeopleLinkID


-Eric Isaacs
From: DavidC on 31 Jul 2010 09:52
Actually, I do want it in the result set because the SSN is for the employEE
and the FedIDNo is for the EmployER. I need to count the Employer of a set of
Employees only once, no matter how many employees they have. If I have it in
the result set then the last record will have the count of unique FedIDNo.
Thanks.
--
David


"Eric Isaacs" wrote:

> Assuming you don't want it as part of your result set (which wouldn't
> make sense because you have the SSN in there) you just need to use
> COUNT(DISTINCT fieldname)...
>
> SELECT COUNT(DISTINCT dbo.ClientInfo.FedIDNo)
> FROM dbo.Timesheets INNER JOIN
> dbo.ClientInfo ON dbo.Timesheets.ClientLinkID =
> dbo.ClientInfo.PeopleLinkID INNER JOIN
> dbo.People INNER JOIN
> dbo.PeopleLink ON dbo.People.PersonID =
> dbo.PeopleLink.PersonID ON dbo.Timesheets.WorkerLinkID =
> dbo.PeopleLink.PeopleLinkID
>
>
> -Eric Isaacs
> .
>
From: Erland Sommarskog on 31 Jul 2010 16:23
DavidC (dlchase(a)lifetimeinc.com) writes:
> Actually, I do want it in the result set because the SSN is for the
> employEE and the FedIDNo is for the EmployER. I need to count the
> Employer of a set of Employees only once, no matter how many employees
> they have. If I have it in the result set then the last record will
> have the count of unique FedIDNo.

It's not entirely clear what output you want, but it seems that
if you add

dense_rank() OVER (ORDER BY dbo.ClientInfo.FedIDNo)

to the result set, each employer will get a unique number.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Query total fish by angler by week
Next: For XML Path / used with Pivot