# Returning Count of Unique Records

• ### Question

• Hey

Basically I have more of a logic problem with trying to extract this data.

MODULID  MODPARTID   LECTUERID
C100           L                      JBW1
C101           L                      JBW1
C102           L                      PKL3
C103           L                      PKL3
C103           P                     PKL3
C104           L                      JBW1

I want to extract the records which are unique within the table via the modulid only occuring once within the table and the MODPARTID must equal P (easy stuff). Now comes the bit I'm stuck on. Once this has happened, I want to COUNT (the function) the amount of MODULID each LECTUERID is equal to, and list them, and example of the end data would be:

LECTUERID     AmountTheyCanTeach
JBW1                4
PKL3                1
PRC3                2

(Note the values aren't accurate, just made them up as an example)

So there were 4 unique MODULID records with the LECTUERID equaling JBW1, therefore 4 is listed

I'd really appreciate help on this, as I've been stuck for hours, here's the table data for anyone who is kind enough to help:

http://pastebin.org/54214
Monday, November 16, 2009 6:05 PM

• SELECT LectuerID,COUNT(ModulID)
FROM TableName
WHERE ModPartID = 'P'
GROUP BY LectuerID

Abdallah, PMP, ITIL, MCTS
• Marked as answer by Monday, November 16, 2009 6:39 PM
Monday, November 16, 2009 6:14 PM
• Adbdallah gave you the correct query; however, if a particular lecturer is associated with a ModulID more than once, then perhaps you need to add the DISTINCT keyword:

SELECT LectuerID,COUNT(DISTINCT ModulID)
FROM TableName
WHERE ModPartID = 'P'
GROUP BY LectuerID

• Marked as answer by Monday, November 16, 2009 6:39 PM
Monday, November 16, 2009 6:18 PM

### All replies

• SELECT LectuerID,COUNT(ModulID)
FROM TableName
WHERE ModPartID = 'P'
GROUP BY LectuerID

Abdallah, PMP, ITIL, MCTS
• Marked as answer by Monday, November 16, 2009 6:39 PM
Monday, November 16, 2009 6:14 PM
• Adbdallah gave you the correct query; however, if a particular lecturer is associated with a ModulID more than once, then perhaps you need to add the DISTINCT keyword:

SELECT LectuerID,COUNT(DISTINCT ModulID)
FROM TableName
WHERE ModPartID = 'P'
GROUP BY LectuerID