locked
Counting Distinct Value in a row and displaying them RRS feed

  • Question

  • User1771308999 posted

    Hello-

    I have two tables:

    Table 1 lists information about each video. Each video has a unique ID, a title, a category, and an answer key.

    Table 2 list all the users, which video they have viewed and attempted to answer.

    The first step I'm trying to do is list Distinct answer key from Table 1. I was able to successfully do so with DISTINCT statement.

    The next step is where I'm stuck: I want to count EACH Distinct answer key. How many each Distinct answer key there is.

    For example:

    Video 1 - Title 1 - Category 1 - Answer Key: 2

    Video 2 - Title 2 - Category 1 - Answer Key: 4

    Video 3 - Title 3 - Category 1 - Answer Key: 2

    Video 4 - Title 4 - Category 1 - Answer Key: 2

    With Distinct statement, I can display just 2 and 4. However how can I could each Distinct value? i.e. there are THREE 2s and ONE 4s

    Any assistance would be greatly appreciated!!

    Tuesday, June 9, 2015 3:45 PM

Answers

  • User-1423995609 posted

    You need to Group By Answer Key and use COUNT() aggregate method to get the resulting count.

    Something alongside these lines (considering column names are the ones from your example)

    SELECT [Answer Key], COUNT(*) AS 'Answer Key Count'
    FROM <TABLE>
    GROUP BY [Answer Key]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 9, 2015 4:56 PM
  • User1771308999 posted

    Found the solution:

    foreach(var matching in db.Query("SELECT AnswerKey, COUNT(*) AS [Counter] FROM TABLE GROUP BY AnswerKey)){
        <div>Answer key: @matching.AnswerKey -- Count: @matching.Counter</div>
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2015 12:36 PM

All replies

  • User-1423995609 posted

    You need to Group By Answer Key and use COUNT() aggregate method to get the resulting count.

    Something alongside these lines (considering column names are the ones from your example)

    SELECT [Answer Key], COUNT(*) AS 'Answer Key Count'
    FROM <TABLE>
    GROUP BY [Answer Key]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 9, 2015 4:56 PM
  • User1771308999 posted

    How can I display the result?

    I'm trying to figure out how to apply this via foreach loop or something else..

    Wednesday, June 10, 2015 8:42 AM
  • User1771308999 posted

    Found the solution:

    foreach(var matching in db.Query("SELECT AnswerKey, COUNT(*) AS [Counter] FROM TABLE GROUP BY AnswerKey)){
        <div>Answer key: @matching.AnswerKey -- Count: @matching.Counter</div>
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2015 12:36 PM