CODEWITHSUNDEEP
mysqlsql
Eddie Johnston
Eddie Johnston

Reputation: 633

Mysql join query

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!

Upvotes: 2

Views: 101

Answers (7)

steve godfrey
steve godfrey

Reputation: 1234

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;

Upvotes: 0

cwallenpoole
cwallenpoole

Reputation: 81988

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;

Upvotes: 1

pkk
pkk

Reputation: 3691

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!

Upvotes: 0

sll
sll

Reputation: 62484

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

Upvotes: 1

ajreal
ajreal

Reputation: 47311

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;

Upvotes: 1

iamkrillin
iamkrillin

Reputation: 6876

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

Upvotes: 0

ace
ace

Reputation: 7583

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

Upvotes: 0

Related Questions