January 21, 2009 at 8:30 am
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!
January 21, 2009 at 8:34 am
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
January 21, 2009 at 8:56 am
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!
January 21, 2009 at 11:20 am
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
January 21, 2009 at 11:33 am
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.
January 21, 2009 at 11:38 am
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
January 21, 2009 at 11:40 am
How can I add a partition Count column to my big query? Is that possible?
Thanks!
January 21, 2009 at 2:56 pm
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