Multiple Counts of Two Tabls SQL & PHP

PHP
1

I’ve Two Tables in One database…
I want to get the Count of the Two Tables Sharing One Value.

Table accommodation
Name | Age | Nationality
mark | 19 | USA
john | 18 | Canada

Table off_campus
Name | Age | Nationality
smith | 21 | USA
maria | 20 | Mexico

Sql Statement


SELECT nationality,nationality count( * )
FROM accommodation, off_campus
GROUP BY nationality,nationality

Mistake :injured:
Which i want it to be as result
USA | 2
Canada | 1
Mexico | 1

Thank you for the help :slight_smile:

2

This shows that the second count is always set to the same value as the first count even though the values should be different.

3 
SELECT nationality
     , COUNT(*)
  FROM ( SELECT nationality
           FROM accommodation
         UNION ALL
         SELECT nationality
           FROM off_campus ) AS u
GROUP 
    BY nationality
4

Thank you deepakg for your information.

Mr.Rudy Limeback
I’m really grateful for your help … The Sql Statement you wrote worked with me. so perfect.
Thank you Master for your SQL consultant…:smiley: