Adding a Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding a Count

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 11:54:35

Hello,

I have a query that creates a list of values. There are labtestcodes that are assigned to different value codes so for example

labtest code, value code
003277 Height
003277 Hgtunt
003277 TotVol
008300 Height
008300 Hgtunt
008300 TotVol

I want to add a count column to this query that will count the number of value codes in each group so for example

003277 Height 1
003277 Hgtunt 2
003277 TotVol 3
008300 Height 1
008300 Hgtunt 2
008300 TotVol 3

Is there a way to do this in the current query I have?

With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

select t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 12:49:24

[code];
With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode[/code]

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 12:58:15

Wow thats really cool thanks for that code. I will definitely be saving that one! :)

quote:

Originally posted by visakh16

;
With labtestcheck (labtestcode)
as 
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)

select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources