How to display the number of people who line in same area and with same characteristics

Question

I m new to sql, so i ask for help is that possible to display the number of people who live in same area with same characteristics. Here is my sample table.

area

AreaID  Area_name
-----------------
001     India 
002     Boston
003     US

Second table :

Person

P_Id  | P_name |  car  | AreaID
------|--------|-------|-------
P01   | John   |  yes  | 001
P02   | Mary   |  null | 001
P03   | Chris  |  yes  | 003
P04   | Jane   |  null | 001
P05   | Ben    |  null | 002
...   
... 
P99   | May    |  null | 002
...

i would like have a query to return number of people which registered in the table.

The answer i hope would be show the count of people have car in same area.

AreaID | Havecar
-------|--------
001    |       2
002    |       7

Or show the most number of people have car from different

AreaID  | numberOfPeople
--------|--------------
002     |         32

are this two possible be make using sql query?

Answer 1

The answer I hope woule be show the count of people have car in same area.

You can use aggregation:

select areaid, count(*) havecar
from person
where car = 'yes'
group by areaid

If you also want to show areas without any car, then you can start from the area table, and bring person with a left join:

select a.areaid, coalesce(p.havecar, 0) havecar
from area
left join (
    select areaid, count(*) havecar
    from person
    where car = 'yes'
    group by areaid
) p on p.areaid = a.areaid

Answer 2

Use a GROUP BY query:

SELECT AREAID, CAR, COUNT(*) AS HAVECAR
  FROM PERSON
  GROUP BY AREAID, CAR
  HAVING CAR = 'yes'
  ORDER BY AREAID, CAR

db<>fiddle here