Get count of each ID in query

  • I have a query that writes out 2 columns and one of those columns is called "dataID".

    An example output might look like:

    dataID | Title

    --------------------

    1 Names

    1 Names

    2 Zones

    3 Books

    4 Papers

    5 Areas

    5 Areas

    5 Areas

    5 Areas

    6 Routes

    Would there be a way to get a third column for the number of times a dataID row was repeated? For example I'd want to see 2 for dataID 1, 4 for data ID 5, etc...

    Thanks!

  • Take a look at "Row_Number" in Books Online and see if that will get you what you want. If not, then a sub-query that uses the Count() function should do it.

    If you can provide the query you have so far, I can help modify it to use either of those.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Here's what I have so far. As you can see, I tried adding a column that COUNTs the nodeIds. But I just get 1 for every row.

    SELECT nl.nodeId, nl.nodeTitle, nl.nodeText, ql.questionText,

    qc.choiceText, qc.patientResponse, nl.mediaID, COUNT(nodeId) AS nodeCount

    FROM nodeList nl

    LEFT JOIN questionList ql ON ql.questionId = nl.questionId

    LEFT JOIN questionChoices qc ON qc.questionId = nl.questionId

    WHERE caseID = 'dddd'

    GROUP BY nl.nodeId, nl.nodeTitle, nl.nodeText, ql.questionText,

    qc.choiceText, qc.patientResponse, nl.mediaID

    However, I just noticed that if write the query like this:

    SELECT nl.nodeId, nl.nodeTitle, COUNT(nodeId) AS nodeCount

    FROM nodeList nl

    LEFT JOIN questionList ql ON ql.questionId = nl.questionId

    LEFT JOIN questionChoices qc ON qc.questionId = nl.questionId

    WHERE caseID = 'dddd'

    GROUP BY nl.nodeId, nl.nodeTitle

    It does work. But I do need those extra columns in there for the query to be useful.

    Thanks!

  • Try something like this:

    SELECT nl.nodeId, nl.nodeTitle, nl.nodeText, ql.questionText,

    qc.choiceText, qc.patientResponse, nl.mediaID,

    (select count(*)

    FROM nodeList nl2

    LEFT JOIN questionList ql ON ql.questionId = nl2.questionId

    LEFT JOIN questionChoices qc ON qc.questionId = nl2.questionId

    WHERE caseID = 'dddd'

    AND nl2.nodeID = nl.nodeID

    AND nl2.noteTitle = nl.noteTitle) AS nodeCount

    FROM nodeList nl

    LEFT JOIN questionList ql ON ql.questionId = nl.questionId

    LEFT JOIN questionChoices qc ON qc.questionId = nl.questionId

    WHERE caseID = 'dddd'

    GROUP BY nl.nodeId, nl.nodeTitle, nl.nodeText, ql.questionText,

    qc.choiceText, qc.patientResponse, nl.mediaID

    The part I added is called a correlated subquery, because part of it (the Where clause) references one of the tables in the outer query.

    Does that get you what you need?

    I don't know if you need the whole From clause in the subquery. You'll need to verify that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (1/21/2009)


    The part I added is called a correlated subquery, because part of it (the Where clause) references one of the tables in the outer query.

    Does that get you what you need?

    I don't know if you need the whole From clause in the subquery. You'll need to verify that.

    It would be easier to use a Count(*) OVER (PARTITION BY nodeID). I think it's also faster, but i have to double check.

  • Gabe (1/21/2009)


    GSquared (1/21/2009)


    The part I added is called a correlated subquery, because part of it (the Where clause) references one of the tables in the outer query.

    Does that get you what you need?

    I don't know if you need the whole From clause in the subquery. You'll need to verify that.

    It would be easier to use a Count(*) OVER (PARTITION BY nodeID). I think it's also faster, but i have to double check.

    Completely forgot you can partition Count these days. Yeah, that's a better solution.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • How can I add a partition Count column to my big query? Is that possible?

    Thanks!

  • dnt know if this is what u want.

    Select DataID, Title, Count(*) Over (Partition by DataID) as Result

    FromProblem1

    Order By DataID

    the result is in this form

    1Names2

    1Names2

    2Books1

    3Movies1

    4Papers1

    5Areas4

    5Areas4

    5Areas4

    5Areas4

    6Box1

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply