Count over group

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
0
Utf
7/30/2010 9:51:03 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
843 Views

Similar Articles

[PageSpeed] 12

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
0
Eric
7/30/2010 11:33:24 PM
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
> .
> 
0
Utf
7/31/2010 1:52:03 PM
DavidC (dlchase@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@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

0
Erland
7/31/2010 8:23:00 PM
Reply:

Similar Artilces:

Create group out of vba code
Hi, When a new database is created a group 'Favorites' exists already. I would like to create additional ones, but with vba code. Furthermore it would be great to assign tables and queries to the groups which have been created. Is that possible? Thanks, Ralf The code below should get you going and the original source is listed if you wish to looked it up further. Public Sub CreateUserGroup(strGroupName As String, _ strPID As String) ' Create a group ' Source: Graham R Seach - MS Access MVP ' http://groups.google.ca/group/microsoft.public.ac...

Record count based on status
I need to write a query that will yeild a report that counts the number of projects based on date fields. For example: a project might be in an upcoming status based on 1. first record date and 2. no engineer start date. If the first record date is 11/01/07 and the engineer start date is 2/1/08, the project is considered to be in upcoming status for November 2007, December 2007, and January 2008. It then is considered to be in design status for February 2008. Can anyone offer any tips on how to write this query? Thanks -KB More rules are needed. It looks like --- no engineer [...

Change grouping on x-axis
Hi, Employee LastMonth CurrentMonth ------------------------------- Mark 31 34 Brian 11 13 Jim 2 1 Derek 9 13 Susan 3 0 I have plotted the above data in a simple clustered column chart with series in columns. So I have 5 series in total - one for each employee. On the x-axis excel groups the data by 'Last Month' and 'Current Month'. How can I group the columns so that each employee's figures are side by side while still keeping the 5 series? I am aware that I could change the chart to hav...

Cycle Counting Serial Number Tracked Products
Hello, Hello, I messed up my first post hit the wrong key. I am looking for ideas on how to cycle count Serial Number Tracked Inventory without increasing workload. I would be interested in know how other companies handle cycle counting of serialized inventory items. Thank in adavnced. Brian Morris Database Administrator >-----Original Message----- >Hello, > > >. > Many customers utilize QualityCount, our Physical Inventory and Cycle Count program along with a handheld barcode scanner to facilitate serial and lot tracked physical inventory and cycle coun...

Routing Group Connector / VPN
we want to add a new exchange 2000 server at a new site (active directory site). this site is connected via VPN to the top location and the server is connected with a routing group connector to the server in the top location. users get mail adresses like user1@site.de at this a site (mailbox at this server) additional to the local adress user1@xyz.local. when we now try to send a message to user1@site.de we get the following NDR : " #5.7.1 smtp;550 5.7.1 Unable to relay for user1@site.de ". when we delete this smtp adress for user1 we can send a message to the internal adres...

Fixed Assets Account Group Integration Manager
After a recent upgrade to GP 10, the Fixed Assets integration for general records created accounts using the same accounts for all items. After comparing IM 9.0 to IM 10.0, I noticed the account group ID field was no longer in Destination Mapping. I then found KB 971836 that mentions the account group ID field can no longer be mapped in IM and they recommend using the company setup or class ID setup to assign default account group IDs. Unfortunately for my client this is not a realistic mapping b/c their chart is structured to be specific to a location and our Asset classes are n...

Count Check Marks
I have a report that counts yes/no. I am using a between date range on it. When I use the following control; =Sum(Abs([ADC_DD-2656-7])) when printing the report I get multiple pages. Could someone assist? If I can format the report to show the Month and year and the totals, that would be Great! hi, lmossolle wrote: > I have a report that counts yes/no. I am using a between date range on it. > When I use the following control; > =Sum(Abs([ADC_DD-2656-7])) Why don't you count it in the record source (the query behind your report)? > when printing the report I get multipl...

Help with Count Please
I need the correct formula to count the following A B 1 5 FW 2 3 G 3 4 G 4 3 R I'd like to count the "B" Column but not when "3" appears in the "A" column. The result of the above would be "2", since column "A" has a "3" in rows "2" and "4". Thanks in advance, Alan You haven't answered Dave's question. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Alan Bernardo" <ifeelyourpain@ihatebush.net> wrote...

Can you print a group schedule? If so, how?
I would like to print a group schedule. Is this possible? ...

excel stack tabs in groups, less scrolling L2R, hovering over gro.
Should be able to "stack" tabs in excel according to groups, hovering over group name pops up the rest of the tabs in that group. This helps organize large/extensize workbooks that have tons of tabs. All tabs/groups are layed out in the same place that the current tab method is now. You could write to MS expressing your concern for a future version: mswish@microsoft.com xlwish@microsoft.com (include excel in the subject so that it gets to the correct group.) Stephen Robb wrote: > > Should be able to "stack" tabs in excel according to groups, hovering over >...

Group By, Max, many to many
I tried posting this before, but it hasn't shown up, sorry if this ends up being a duplicate. I have the following tables and fields: Catchments (UID) Districts (DistID, Name) Lookup (UID, DistID, percent) UID, DistID, UID and DistID are the primary keys To give you an idea of what the data mean; the percent field refers to the percentage of each catchment that is made up of a particular district. Catchments and Districts have a many to many relationship. I would like to create a query that will take the Max percentage for each catchment UID and return the name of t...

How do I count the number of records within a date range
How do I count the number of records within a date range. eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 = 2 =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") Format cell as General "Mike" wrote: > How do I count the number of records within a date range. > eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 > = 2 try =sumproduct((year(daterng)=2003)*1) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mike" <Mike@dis...

Counting Filtered Results Using Autofilter
I frequently use Autofilter to count the number of instances in a column of data. Usually, when I apply the autofilter to the column heading and execute a search, the number of results appears in a bar at the lower left hand corner of the Excel window, similar to "127 of 658 items". Periodically, this changes to "Ready" and no longer returns the number of instances of the filtered results. Any idea why this happens, how can I prevent it, and how do I make it starting counting again? Thanks John You sure you're seeing Ready? Debra Dalgleish exp...

Count occurrences
Hello all, I am very new to Access, am doing an online course and learning as I go. I am stuck with one query and I'm hoping someone can help me. I have a sales table for which I am required to produce a query which tells the most popular artists and the number of sales they have made. The fields in the table are:- Invoice number, product code, artist code, date, payment type. The query is on the Artist Code field, but for the life of me I cannot get it to show just the top 5 Artists and their sales count. Hoping for help - written slowly 'cos I'm very new at this. ...

Counting Text Entries
Hi everyone, I have a large database of information which includes columns of text entries. Is there a way of counting these text entries? e.g.: HBO SL SL MN RTA HBO SL SL MN RTA HBO SL SL MN RTA Counting the number of HBO, RTA,SL entries etc. The database consists of 26 worksheets, is it also possible to get the total results from all worksheets to appear on a single summary page? Thanks very much for any help. Jack -- JackJJW ------------------------------------------------------------------------ JackJJW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...

View question: related to grouping and sorting
I have a view where I am using "Group By" on a single field. I would like to have the view sorted based on the number of messages in the groups. Can this be done? Thanks, Frank ...

Error message when sending to a large Distribution group
When sending a message to a new Dist Group I'ver just set up, the email sits in the Outbox and won't send out. On Send/Receive, I get the following error message:- Task 'mail.golfholidayscornwall.co.uk - Sending' reported error (0x800CCC0F) : 'The connection to the server was interrupted. If this problem continues, contact your server administrator or Internet service provider (ISP). The server responded: 421 Error: too many errors' Is it that the Dist Group is too large? That is a possibility. Try recreating the distribution group or send it it t...

Sub menus within groups
Hi Is it possible to create such a thing a group which acts like a familly tree. I have many system driver cd's and would like to create an easier way to find certain drivers. I would like to have a list of manufacturers and driver types which open out to show subdirectories something like the example shown below. -Asus -Motherboard -A76DR +VGA +Sound +Lan -Gigabyte -Motherboard -G7VMM ...

Counting & Conditions
Hi to evrybody, I have 13 cells in the same row as follows: J14, M14, P14, S14, V14, Y14, AB14, AE14, AH14, AK14, AN14, AQ14, AT14 Each of them contains a number which is a result from a calculation. In Cell BD14, I want a value depending on the number of cells with the following condition (counting): If any 4 or more of the 13 cells has a number < 50 ---> put "Very Slow" in cell DB14 If none of the cells has number < 50 ---> put "Very Fast" in cell BD14 If LESS than 4 cells has number < 50 ---> put "Moderate" in cell BD14 Any suggestions ...

Routing Group Config
We originally had an Exchange 5.5 organization with 2 sites - London & Canberra. We migrated to Exchange 2000 & moved users to new servers at each site, basically by adding E2K servers at each site, then doing a mailbox move. We then upgraded both servers to Exchange 2003 Enterprise & have changed to native mode This has left us with 2 Administrative Groups (Canberra & London), each with a routing group containing the servers located at those sites. There is a routing group connector between Canberra & Sydney & the sites are connected by a VPN connection between firewa...

Distribution Group Member Limit 1000
I'm having trouble with a Distribution group on our system. We're running Exchange 2003, on a Windows 2003 Server. In Active Directory I have a list of users (3354 users total) that I need to put in an 'All Users' distribution group. I have no problem adding the users, but after a short amount of time the distribution group cuts down to 1000 users. If I look at the users that are in the list, they are the first 1000 users that had been added to the system. I've been unable to find a solution or even anyone with the same problem. Does anyone have any ideas? Thanks for...

How do I set up groups of email contacts---like friends or relati.
I am transferring from Eudora. I am a travel writer and have groups of contacts from many countries. How do I re-establish (and most important of all ..what is the easiest way to transfer these groups?) what was called addressbooks in Eudora Travellady <Travellady@discussions.microsoft.com> wrote: > I am transferring from Eudora. > I am a travel writer and have groups of contacts from many countries. > > How do I re-establish (and most important of all ..what is the > easiest way to transfer these groups?) what was called addressbooks > in Eudora Since Outlook Ex...

Recovery Storage Group question #2
Hello I have a exchange 2003 w/ sp1 server running. I setup a RSG and added database from my production exchange box. I restore a previous backup to RSG and can use exmerge to extract mailboxes and merge them into the production database. I also have a another exchange 2003 w/ sp1 running as backup. There is no mailbox in this server. I noticed if I setup a RSG and restore to this RSG, I cannot use exmerge. My question is does exmerge works with RSG only on the server that actual mailboxes exist? If this is not the case, is there a way to setup RSG on a different server so exmerge can b...

How to monitor group policy changes in real time?
Hi all, I'm interested in developing a real-time monitor that saves all changes to group policy objects. Hopefully, this module will be notified if any admins add/delete/modify GPOs. Can anyone show me some pointers? Thanks! -Simon Simon, Simon wrote: > I'm interested in developing a real-time monitor that saves all > changes to group policy objects. Hopefully, this module will be > notified if any admins add/delete/modify GPOs. > > Can anyone show me some pointers? If yoiu're looking for some change control mechanism, AGPM (Advanced Group...

Count records in 10 minute time period
using access 2003 is have the following query SELECT [AlarmData Table Filtered by Date].[Event Type], [AlarmData Table Filtered by Date].State, [AlarmData Table Filtered by Date].[Date/Time*] FROM [AlarmData Table Filtered by Date] GROUP BY [AlarmData Table Filtered by Date].[Event Type], [AlarmData Table Filtered by Date].State, [AlarmData Table Filtered by Date].[Date/Time*] HAVING ((([AlarmData Table Filtered by Date].[Event Type])="ALARM") AND (([AlarmData Table Filtered by Date].State)="ACT/UNACK")); this gives me something like Event Type State Date/...