Pivot table on two SQL table, one with 5m+ rows?

Question

Table cities has 2k rows

INT id
VARCHAR name

Table persons has 5M rows

VARCHAR person_id
INT city_id` (Foreign Key to table cities.id)

Note this is a one-to-many relationship.

I would like to get the following table:

VARCHAR city_name
INT count

in descending order by count

For example, if table city has 3 rows

id    city_name
1     San Francisco
2     Los Angeles
3     New York

and table persons has 3M rows that point to San Francisco, and 2M rows that point to Los Angeles, I would like the result to be:

city_name       count
San Francisco   3,000,000
Los Angeles     2,000,000
New York        0

Is this possible to do with just a SQL query when I have 5m rows?

score 2 · Accepted Answer · answered Feb 25 '14 at 08:46

2

Would this not do the trick?

SELECT 
    c.city_name, 
    COUNT(p.person_id) AS `count` 
FROM 
    cities c 
    LEFT OUTER JOIN persons p ON c.id = p.city_id 
GROUP BY 
    c.city_name

answered Feb 25 '14 at 08:46

Dave Child

7,573
2
25
37

It will do providing there is and index on persons.city_id – vadchen Feb 25 '14 at 08:48
It would still work without the index, just would be very slow. – Dave Child Feb 25 '14 at 08:49

Pivot table on two SQL table, one with 5m+ rows?

1 Answers1