Ayaz Amir Ayaz Amir - 7 months ago 8
SQL Question

SQL sub query. Dont know how to start

The table is called

PEOPLE
and has the columns: id, fname, lname, state, jobtitle, salary, cat

I want to display the 50 states, the number of people in the state with a Cat value of Y, the number of people in the state with a Cat value of N and the total number of people in the state. The four column headings should be State, Yes, No and Total.

I know that we are supposed to use sub queries but not sure how to begin.

Answer

Here:

SELECT upper(p.state) State,
     (SELECT count(*) from PEOPLE p2 where p2.state = p.state and p2.cat = 'Y') Yes,
     (SELECT count(*) from PEOPLE p3 where p3.state = p.state and p3.cat = 'N') No,
     count(*) Total
FROM PEOPLE p
GROUP BY upper(state)

If you have any doubt about it, fell free to ask.