CODEWITHSUNDEEP
sqlproc-sql
user3613720
user3613720

Reputation: 47

ERROR: Subquery evaluated to more than one row in Proc SQL

I have a table -

Name    Job Group     Profession
John    Engineering    Yes
Shawn   Biology        Yes
Meg     Biology        No
Mike    Engineering    Yes   
Meg     Biology        Yes
John    Engineering    Yes
Mike    Biology        Yes
John    Engineering    Yes
Meg     Biology        Yes

I want to count Profession(either Yes or No) for same names corresponding to Job group. So the output I want is -

Output

Name   Engineering  Biology 
John      2            1
Mike      1            1
Shawn                  1
Meg                    2

I tried with this code but no luck -

CREATE TABLE Profile AS   
SELECT P.Name
        ,sum(P.Profession = "Yes") as Engineering_Yes
        ,sum(P.Profession = "Yes") as Engineering_No
        ,sum(P.Profession = "No") as Biology_Yes
        ,sum(P.Profession = "No") as Biology_Yes            
FROM Profile P

I tried to add Job Group column using union select but no luck

I am new to Proc SQL.I tried to search same example but cannot find it for Proc SQL. Please help.

Upvotes: 0

Views: 818

Answers (2)

shawnt00
shawnt00

Reputation: 17925

select P.Name,
    count(case when "Job Group" = 'Engineering' then 1 end) as Engineering,
    count(case when "Job Group" = 'Biology' then 1 end) as Biology,
from Profile P
where Profession = 'Yes'
group by Name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Well, you can do use GROUP BY:

CREATE TABLE Profile AS   
    SELECT P.Name,
           sum(case when P.Job_Group = 'Engineering' and P.Profession = 'Yes' and  then 1 else 0 end) as Engineering_Yes,
           sum(case when P.Job_Group = 'Engineering' and P.Profession = 'No' then 1 else 0 end) as Engineering_No,
           sum(case when P.Job_Group = 'Biology' and P.Profession = 'Yes' then 1 else 0 end) as Biology_Yes,
           sum(case when P.Job_Group = 'Biology' and P.Profession = 'No' then 1 else 0 end) as Biology_No           
    FROM Profile P
    GROUP BY P.Name;

Upvotes: 1

Related Questions