none
Count sql statement

    Question

  • Hi I have the following

    DIAGN	ICD_Diagnosis	1
    PROCE	ICD_Procedure	1
    PROCE	ICD_Procedure	2
    DIAGN	ICD_Diagnosis	2

    Headers

    Diag Diagnosis EpiID

    I want to count diagnosis for each EpiID and Count Procedure for each EpiID

    Any help would be great

    thanks

     


    David
    Monday, April 04, 2011 12:33 AM

Answers

  • Try the following:

    select sum(case when Diag = 'DIAGN' then 1 else 0 end) as [Count Diagnosis],
    
    select sum(case when Diag = 'PROCE' then 1 else 0 end) as [Count Procedure],
    
    EpiID
    
    from Diagnosis
    
    GROUP BY EpiID

    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Monday, April 04, 2011 1:03 AM

All replies

  • Try the following:

    select sum(case when Diag = 'DIAGN' then 1 else 0 end) as [Count Diagnosis],
    
    select sum(case when Diag = 'PROCE' then 1 else 0 end) as [Count Procedure],
    
    EpiID
    
    from Diagnosis
    
    GROUP BY EpiID

    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Monday, April 04, 2011 1:03 AM
  • On top of  Naomi's solution, you may also try:


    Declare @mytable table
    (
    diagID varchar(10) not null,
    Diagnosis varchar(50),
    EpID Int not Null
    )
    Insert into @mytable values 
    ('Diag', 'ICD_Diagnosis',1),
    ('Proce','ICD_Procedure',1),
    ('Diag', 'ICD_Diagnosis',2),
    ('Proce','ICD_Procedure',2)
    
    Select	EpID, [Diag], Proce
    From	@mytable
    Pivot	(	Count(Diagnosis)
    			For DiagID In ([Diag],[Proce])
    		) myPiv
    Monday, April 04, 2011 2:05 AM
  • Thanks Steven,

    Will test this out later

     


    David
    Monday, April 04, 2011 5:28 AM