Mysql join query

Question

Im sure there's a simple solution to this but I can't figure it out at all.

I have a table of photos that have states and countries associated with them. I have two simple queries, one to get the number of photos per country:

select country, count(*) from photos group by country;

+-------------+----------+
| country     | count(*) |
+-------------+----------+
| Argentina   |        6 |
| Australia   |        5 |
| Chile       |        3 |
| Ecuador     |       10 |
| France      |        1 |
| Hong Kong   |        4 |
| Indonesia   |        6 |
| Ireland     |       16 |
| Malaysia    |        1 |
| New Zealand |        3 |
| Peru        |        8 |
+-------------+----------+

The other gets the number of photos per state (and country):

select country, state, count(*) from photos group by country, state;

+-------------+---------------------+----------+
| country     | state               | count(*) |
+-------------+---------------------+----------+
| Argentina   | Misiones            |        2 |
| Argentina   | Salta               |        4 |
| Australia   | New South Wales     |        1 |
| Australia   | Victoria            |        4 |
| Chile       | Antofagasta         |        3 |
| Ecuador     | Galapagos           |        5 |
| Ecuador     | Sucumbios           |        5 |
| France      | Aquitaine           |        1 |
| Hong Kong   | Hong Kong           |        4 |
| Indonesia   | Bali                |        3 |
| Indonesia   | Nusa Tenggara Barat |        1 |
| Indonesia   | Yogyakarta          |        2 |
| Ireland     | Antrim              |        1 |
| Ireland     | Cork                |        1 |
| Ireland     | Derry               |        2 |
| Ireland     | Donegal             |        8 |
| Ireland     | Kerry               |        1 |
| Ireland     | Sligo               |        1 |
| Ireland     | Waterford           |        1 |
| Ireland     | Wexford             |        1 |
| Malaysia    | Sabah               |        1 |
| New Zealand | Manawatu            |        2 |
| New Zealand | Westland            |        1 |
| Peru        | Cusco               |        6 |
| Peru        | La Libertad         |        2 |
+-------------+---------------------+----------+

What I'd like to do is to perform a join on these queries that results in the following:

+-------------+---------------------+----------+----------+
| country     | state               |   statet | countryt |
+-------------+---------------------+----------+----------+
| Argentina   | Misiones            |        2 |        6 |
| Argentina   | Salta               |        4 |        6 |
| Australia   | New South Wales     |        1 |        5 |
| Australia   | Victoria            |        4 |        5 |
| Chile       | Antofagasta         |        3 |        3 |
| Ecuador     | Galapagos           |        5 |       10 |
| Ecuador     | Sucumbios           |        5 |       10 |
| France      | Aquitaine           |        1 |        1 |
| Hong Kong   | Hong Kong           |        4 |        4 |
| Indonesia   | Bali                |        3 |        6 |
| Indonesia   | Nusa Tenggara Barat |        1 |        6 |
| Indonesia   | Yogyakarta          |        2 |        6 |
| Ireland     | Antrim              |        1 |       16 |
| Ireland     | Cork                |        1 |       16 |
| Ireland     | Derry               |        2 |       16 |
| Ireland     | Donegal             |        8 |       16 |
| Ireland     | Kerry               |        1 |       16 |
| Ireland     | Sligo               |        1 |       16 |
| Ireland     | Waterford           |        1 |       16 |
| Ireland     | Wexford             |        1 |       16 |
| Malaysia    | Sabah               |        1 |        1 |
| New Zealand | Manawatu            |        2 |        3 |
| New Zealand | Westland            |        1 |        3 |
| Peru        | Cusco               |        6 |        8 |
| Peru        | La Libertad         |        2 |        8 |
+-------------+---------------------+----------+----------+

I've been playing around with joins all morning and can't figure it out, any pointers would be really appreciated!

ajreal · Accepted Answer · 2011-08-11 13:49:10Z

1

select cs.country, cs.state, count(*) as statet, c.countryt
from photos cs
left join (select country, count(*) countryt from photos group by country) as c
on c.country=cs.country
group by cs.country, cs.state;

answered Aug 11, 2011 at 13:49

ajreal

47.4k11 gold badges98 silver badges119 bronze badges

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

Comments

sll · Accepted Answer · 2011-08-11 13:52:43Z

1

Does it work as you expecting?

 SELECT country, state, COUNT(DISTINCT country), COUNT(state)
  FROM photos 
  GROUP BY country, state

Also, try to remove DISTINCT keyword if you need to count the same country per different states

edited Aug 11, 2011 at 13:52

answered Aug 11, 2011 at 13:40

sll

62.7k22 gold badges109 silver badges157 bronze badges

3 Comments

sll Over a year ago

May be, it returns COUNTRY - STATE - NUMBER_OF_COUNTRIES - NUMBER_OF_STATE_PER_COUNTRY, also by removing DISTING country you may get more expanded view

ajreal Over a year ago

this will always return 1 for country, because every records in country-state is always having same country

sll Over a year ago

Absolutely, and for me it makes more sense rather than return for the unique country name COUNT more thatn 1 ;) But if question author want to see count of unique pairs - then DISTINCT should be removed

cwallenpoole · Accepted Answer · 2011-08-11 16:33:18Z

1

Personally, I'd go subquery:

select 
     country, 
     state, 
     count(*) as state, 
     (select count(*) from photos inr 
          where inr.country = otr.country) 
from photos otr group by country, state;

edited Aug 11, 2011 at 16:33

answered Aug 11, 2011 at 13:39

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

1 Comment

adamcodes Over a year ago

WHERE needs to replace HAVING in the subquery.

ace · Accepted Answer · 2011-08-11 13:38:37Z

0

This should do the trick.

SELECT country_state.country, country_state.state, 
       country_state.statet, country.countryt
FROM
(SELECT country, count(*) as countryt FROM photos group by country) AS country
JOIN
(SELECT country, state, count(*) as statet FROM photos group by country, state) AS country_state ON country.country = country_state.country

answered Aug 11, 2011 at 13:38

ace

7,6513 gold badges25 silver badges28 bronze badges

Comments

iamkrillin · Accepted Answer · 2011-08-11 13:39:20Z

0

try

select * 
from (
    select country, state, count(*) from photos group by country, state
) a
inner join (
    select country, count(*) from photos group by country
) b on a.country = b.country

answered Aug 11, 2011 at 13:39

iamkrillin

6,8761 gold badge27 silver badges52 bronze badges

Comments

pkk · Accepted Answer · 2011-08-11 14:08:14Z

0

My solution, tested and should give the exact result:

SELECT a.country AS country, p.state AS state, COUNT(*) AS statet, a.count AS countryt 
FROM ( SELECT p1.country, COUNT(*) AS count FROM photos p1 GROUP BY p1.country) a 
JOIN photos p ON (p.country = a.country) 
GROUP BY p.state;

Enjoy!

answered Aug 11, 2011 at 14:08

pkk

3,7092 gold badges23 silver badges29 bronze badges

Comments

steve godfrey · Accepted Answer · 2011-08-11 21:47:34Z

0

To avoid having to query your table twice, you could use analytics.

select country
  , state
  ,group_by_country
  ,group_by_state
from (select country
       , state
       , count(*) over  (partition by country) group_by_country
       , count(*) over  (partition by country,state) group_by_state
       ,row_number() over (partition by country,state order by country,state) row_num
  from photos
  order by country,state)
where row_num = 1;

answered Aug 11, 2011 at 21:47

steve godfrey

1,2447 silver badges15 bronze badges

Collectives™ on Stack Overflow

Mysql join query

7 Answers 7

Comments

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related