Member Avatar for kishan112
kishan112 0 Light Poster

Hey guys,
I have the following table:
persons
- name
- street
- number
- regioncode
- state
- city

I want to count the amount of people per region per street. I can do it with the following query:

SELECT state, city, regioncode, street, COUNT(*) AS personsStreetRegion FROM persons GROUP BY `street`,`region`,`number`

Now I want to know how many families (a family is all the people living in one house) there are in every street.
I can do that with the following query:

SELECT state, city, regioncode, street, COUNT(*) AS familiesStreetRegion FROM persons GROUP BY `street`,`region`

I want to combine these and get an output with state, city, regioncode, street, personsStreetRegion, familiesStreetRegion

Edit: My second query is not returning the result I want to have. I want the number of families staying in 1 street, not number of persons staying in 1 street

Edited by kishan112 because: n/a
  • 1 Contributor
  • 0 Replies
  • 52 Views
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.