CODEWITHSUNDEEP
sqlsubquery
daaq
daaq

Reputation: 11

Having trouble with the subquery in this code

I'm trying to run this code for an assignment for a class I've got. The "x" at the end of my subquery keeps on giving me errors and I can't wrap my head around why this is.

The goal of this assignment is to count (by age group) the number of reports that Carditis was a symptom after receiving a COVID shot.

Thanks in advance

Select agegroup, sum(case when died= 'Y' then 1 else 0 end) as Deaths
From (Select *,

Case 
    when age<=2 then 'infant' 
    when age<18 then 'juvenile'
    when age<35 then 'adult'
    when age<65 then 'old adult'
    when age>=65 then 'senior'
    else 'unknown' end as agegroup
from dbo.symptoms as s
    join dbo.vaersvax as v on s.vaers_id=v.vaers_id
    join dbo.patient as p on s.vaers_id=p.vaers_id
    where v.vax_type='COVID19' and OneVax='Y' and symptom='Carditis'
) as x
Group By agegroup
Order By avg(age)

Upvotes: 1

Views: 47

Answers (2)

mbd
mbd

Reputation: 356

As @Schmocken already said, you can't perform a SELECT FROM a subquery that returns more than one column with the same name. As I suppose from your external query, this would do the job for you:

Select agegroup, sum(case when died= 'Y' then 1 else 0 end) as Deaths
From (Select died, age,
Case 
    when age<=2 then 'infant' 
    when age<18 then 'juvenile'
    when age<35 then 'adult'
    when age<65 then 'old adult'
    when age>=65 then 'senior'
    else 'unknown' end as agegroup
from dbo.symptoms as s
    join dbo.vaersvax as v on s.vaers_id=v.vaers_id
    join dbo.patient as p on s.vaers_id=p.vaers_id
    where v.vax_type='COVID19' and OneVax='Y' and symptom='Carditis'
) as x
Group By agegroup
Order By avg(age)

Upvotes: 1

Schmocken
Schmocken

Reputation: 613

By using Select * you have specified the same column name to be returned more than once. As an example, you are returning both s.vaers_id and v.vaers_id, which are the same. This is not allowed; a subquery must return a unique set of column names. You could return s.* successfully, but not all columns from all tables.

Upvotes: 0

Related Questions