Ayaz Amir - 1 year ago 39

SQL Question

The table is called

`PEOPLE`

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.

Source (Stackoverflow)