question

docmom123 avatar image
docmom123 asked

Query with Subquery returning only column names

I have the query below, but when I run it, I can only get column names to return. Can someone please help me??? For each subject there are multiple values at each marker. I want an average, by date, for each subject at each marker. I have spent literally hours on this... Thanks in advance for any help!!!! Lori

USE ALS_Smash_BBP
go

select 
    SubjectID
    ,clinicdate
    ,avg(JR_RBHo) AVGJR_RBHo
    ,avg(LL_JRo) AVGLL_JRo
    ,avg(LL_RBHo) AVGLL_RBHo
    ,avg(RC_LCo) AVGRC_LCo
    ,avg(UL_RBHo) AVGUL_RBHo
    ,avg(UL_LLo) AVGUL_LLo
    ,avg(UL_RC_LL_LCo) AVGUL_RC_LL_LCo
from (
    select SubjectID
        ,clinicdate
        ,case Marker when 'JR_RBHo' then MaxSpd else NULL end JR_RBHo
        ,case Marker when 'LL_JRo' then MaxSpd else NULL end LL_JRo
        ,case Marker when 'LL_RBHo' then MaxSpd else NULL end LL_RBHo
        ,case Marker when 'RC_LCo' then MaxSpd else NULL end RC_LCo
        ,case Marker when 'UL_RBHo' then MaxSpd else NULL end UL_RBHo
        ,case Marker when 'UL_LLo' then MaxSpd else NULL end UL_LLo
        ,case Marker when 'UL_RC_LL_LCo' then MaxSpd else NULL end UL_RC_LL_LCo
    from (
        select distinct SubjectID, clinicdate, Marker, MaxSpd
        from dbo.BBP_JerkCost_STATS051810
        where Marker IN ('JR_RBHo','LL_JRo','LL_RBHo','RC_LCo','UL_RBHo','UL_LLo','UL_RC_LL_LCo')
        and MaxSpd is not NULL AND SubjectID = '0069'
    ) x
) xx
group by SubjectID, clinicdate
sub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

possibly change your case statements to be

,case Marker when 'JR_RBHo' then MaxSpd else 0 end JR_RBHo

i.e. use 0 rather than null, as aggregating a null value will give null, depending on your server settings

Edit :-> If that doesn't help, make sure each of the sub-selects are returning the data as you would expect

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

actually thinking about it, AVG excludes NULLs, so that shouldn't be causnig the issue....
0 Likes 0 ·
Docmom123 1 avatar image
Docmom123 1 answered

Thanks Kev..... Now today...it ran fine, so I'm not sure what I was doing the other day! Thanks for trying!!! Lori

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.