loop within loop mysql count

Question

I've setup a few tables to represent different products as some products have different attributes to others.

I am trying to do a count of how many items are listed from a specific city. My sql statement is as follows:

Select count(*) as count from location, tbl_books where location.city = tbl_books.location && location.city = 'London' && location.country = 'United Kingdom'

Select count(*) as count from location, tbl_clothes where location.city = tbl_clothes.location && location.city = 'London' && location.country = 'United Kingdom'

This loops to count results in all cities within the country. These loops represents the inner loop. The outer loop loops through the different category tables.

I find this is not very efficient and was wondering whether anyone might be able to suggest any way of dealing with this problem. I was thinking of just creating a master table with cities and category fields and just incrementing the count in the master table everytime a new item is added from a particular city.

I can't see any loops there....

andrewsi
– andrewsi

2012-06-12 20:11:29 +00:00
Commented Jun 12, 2012 at 20:11 — andrewsi
– andrewsi, Commented Jun 12, 2012 at 20:11

bfavaretto · Accepted Answer · 2012-06-12 20:16:21Z

1

This query should give you the book counts for all cities and countries, no loops needed:

SELECT 
    location.country, 
    location.city,
    COUNT(*) as `count` 
FROM locaton
INNER JOIN tbl_books ON location.city = tbl_books.location 
GROUP BY location.country, location.city
ORDER BY location.country, location.city

Results will look like this:

country          |   city    |   count
-----------------+-----------+-----------
France           | Paris     |  1
United Kingdom   | Belfast   |  2
United Kingdom   | London    |  3
...

answered Jun 12, 2012 at 20:16

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1114795 Over a year ago

This is great, how would I go about adding these counts to counts from other category tables?

bfavaretto Over a year ago

I'm not sure, that depends on how many tables and their structure. Maybe you should ask a separate question about that.

Christian Specht · Accepted Answer · 2012-06-12 20:14:17Z

1

This will sum up all books by city, for all cities in the UK (out of my head, untested):

select tbl_books.location, count(tbl_books.location) as count
from location inner join tbl_books on location.city = tbl_books.location
where location.country = 'United Kingdom'
group by tbl_books.location

The result should look something like this:

location  |  count
--------------------
London    |  5
--------------------
Newcastle |  1
--------------------
...       | ...

answered Jun 12, 2012 at 20:14

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Comments

octopusgrabbus · Accepted Answer · 2012-06-12 23:01:58Z

1

The following SQL query will return the count and location.city values as one query by joining the location table.city key with the tbl_books.location key.

Select count(*) as count, location.city 
from location, tbl_books 
where location.city = tbl_books.location
group by location.city

edited Jun 12, 2012 at 23:01

octopusgrabbus

10.7k15 gold badges75 silver badges137 bronze badges

answered Jun 12, 2012 at 20:13

Scott Saunders

30.5k14 gold badges60 silver badges66 bronze badges

Collectives™ on Stack Overflow

loop within loop mysql count

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related