CODEWITHSUNDEEP
mysql
xrcwrn
xrcwrn

Reputation: 5327

calculating no of users at each place

I have a table place which will store details about place like

Place(id,name,detail,type,Pid);//pid is parent place id

User(id,name,detail);

user_place(uid,pid);//primary key of user and place table

Here A place can be area,city,state or country.

I want to track no of users for perticular place

like for area I want to fetch total users of area i.e. 400

again for city I want to fetch total user(that will be total user of all area of city) i.e 50000(for city).

Similar for state and country.

for calculation above data, Is place table structure is correct? or any modification is required? How to calculate no of users according to city,state,country?

Upvotes: 0

Views: 241

Answers (1)

pala_
pala_

Reputation: 9010

Assumptions: a user is in an area. an area is in a city, a city is in a country.

total number of users in area Arkham:

select count(up.uid)
  from place p
    inner join user_place up
      on p.id = up.pid
  where p.type = 'Area' and p.name = 'Arkham';

Total number of users in the city gotham, which has multiple areas.

select count(up.uid)
  from place p
    inner join place p2
      on p.pid = p2.id and p.type = 'Area' and p2.type = 'City'
    inner join user_place up
      on p.id = up.pid
    where p2.name = 'gotham'

There is a demo of this here: http://sqlfiddle.com/#!9/14238/5

Upvotes: 1

Related Questions