none
Returning Count of Unique Records RRS feed

  • 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

Answers

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


    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by Bacanze 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

    It depends on your data and what your requirements are.
    --Brad (My Blog)
    • Marked as answer by Bacanze Monday, November 16, 2009 6:39 PM
    Monday, November 16, 2009 6:18 PM
    Moderator

All replies

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


    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by Bacanze 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

    It depends on your data and what your requirements are.
    --Brad (My Blog)
    • Marked as answer by Bacanze Monday, November 16, 2009 6:39 PM
    Monday, November 16, 2009 6:18 PM
    Moderator
  • Thanks for the query, I can't believe how much I overcomplicated it :P
    Monday, November 16, 2009 6:40 PM