Subquery only returns 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
Software/Hardware used:
Software/Hardware used:
ASKED:
May 27, 2010 12:42 PM
UPDATED:
May 27, 2010 4:58 PM
Answer Wiki:
"<i>I can only get column names to return</i>"
This means that there are no records matching your WHERE clause.
Then only query that includes a WHERE clause is this:
<pre>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' </pre>
And it is not returning any rows.
I would try removing all conditions from this query's WHERE clause, and start adding them back one at a time, to see which is the one causing the problem.
-CarlosDL
To see all answers submitted to the Answer Wiki: View Answer History.
Carlos is absolutely correct. When building a query start small; add conditions and subqueries, etc slowly (maybe even one at a time) to build up your query to the final result.
Also, having *known* test data is a big help. That way you would know if there is data to be found that matches your conditions.
That makes perfect senne…and seems so simple now that you say it! I will start over on the where and build back up! Thanks so much!!
my be you do not have any values available as out put. if it is showing column name only still your answer is good.