October 21, 2015 at 3:29 pm
Hi
I am using the following "MySortedRows" routine
...
select count(icd10)as counticd10 ,icd10
,ROW_NUMBER() over (order by count(icd10) desc) as Row
,case when ROW_NUMBER() over (order by count(icd10) desc) > 5 then 'xx' end as others
from MySortedRows
where rownum = 1 AND ICD10 IS NOT NULL
group by ICD10
order by COUNT(icd10) desc
to get data below:
counticd10icd10Rowothers
61,F43.10,1,NULL
44,F90.2,2,NULL
29,F90.9,3,NULL
23,F41.9,4,NULL
19,F41.1,5,NULL
18,F32.9,6,xx
16,F43.25,10,xx
14,F43.20,11,xx
what I would like to do is add a column
where if the row is <=5 then use the value in the count field
so
first row would have 61, second 44 ...
I would like a running count after the 5th row or just a total count in the sixth row
because all I need are the total counts for anything over the fifth row.
for the example above my sixth row would have 48 for a count(18+16+14)
Thanks and hope I wasn't too confusing...
October 22, 2015 at 7:55 am
jbalbo (10/21/2015)
HiI am using the following "MySortedRows" routine
...
select count(icd10)as counticd10 ,icd10
,ROW_NUMBER() over (order by count(icd10) desc) as Row
,case when ROW_NUMBER() over (order by count(icd10) desc) > 5 then 'xx' end as others
from MySortedRows
where rownum = 1 AND ICD10 IS NOT NULL
group by ICD10
order by COUNT(icd10) desc
to get data below:
counticd10icd10Rowothers
61,F43.10,1,NULL
44,F90.2,2,NULL
29,F90.9,3,NULL
23,F41.9,4,NULL
19,F41.1,5,NULL
18,F32.9,6,xx
16,F43.25,10,xx
14,F43.20,11,xx
what I would like to do is add a column
where if the row is <=5 then use the value in the count field
so
first row would have 61, second 44 ...
I would like a running count after the 5th row or just a total count in the sixth row
because all I need are the total counts for anything over the fifth row.
for the example above my sixth row would have 48 for a count(18+16+14)
Thanks and hope I wasn't too confusing...
With nearly 400 points you aren't new around here. Please post consumable ddl and sample data in addition to desired output. Please take a few minutes and read the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2015 at 8:34 am
jbalbo (10/21/2015)
HiI am using the following "MySortedRows" routine
...
select count(icd10)as counticd10 ,icd10
,ROW_NUMBER() over (order by count(icd10) desc) as Row
,case when ROW_NUMBER() over (order by count(icd10) desc) > 5 then 'xx' end as others
from MySortedRows
where rownum = 1 AND ICD10 IS NOT NULL
group by ICD10
order by COUNT(icd10) desc
to get data below:
counticd10icd10Rowothers
61,F43.10,1,NULL
44,F90.2,2,NULL
29,F90.9,3,NULL
23,F41.9,4,NULL
19,F41.1,5,NULL
18,F32.9,6,xx
16,F43.25,10,xx
14,F43.20,11,xx
what I would like to do is add a column
where if the row is <=5 then use the value in the count field
so
first row would have 61, second 44 ...
I would like a running count after the 5th row or just a total count in the sixth row
because all I need are the total counts for anything over the fifth row.
for the example above my sixth row would have 48 for a count(18+16+14)
Thanks and hope I wasn't too confusing...
Agree with Sean that an actual consumable script would be useful. What would the 7th row have for "xx" 30? (16 + 14)
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply