Question 243435 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql query string get 2 counts from selects

i’m trying to create a query that allows me to get 2 counts from selects.

I have a table (name "warehouses") with the following design and data:

nation city
italy roma
germany berlin
italy roma
france paris
italy torino
italy firenze

I would like to get the count of nation and the related count of city

example:

nation: italy 4
city: roma 2

nation: italy 4
city: torino 1

nation: italy 4
city: firenze 1

my attempt

select nation, city, count (nation), count (city)
from warehouses

but result is different than I would like…

select nation, city, count (nation), count (city)
from warehouses
Tags:

2

Answers


  1. 0 
    SELECT w.nation, w.city, c.nation_count, COUNT(w.city) as city_count
FROM warehouses w
JOIN (
    SELECT nation, COUNT(*) as nation_count
    FROM warehouses
    GROUP BY nation
) c ON w.nation = c.nation
GROUP BY w.nation, w.city, c.nation_count;
    Login or Signup to reply.
  2. 0

    The easiest approch to get both city and country counts is to use WITH ROLLUP:

    select 
  case when grouping(nation) then 'all nations' else nation end as nation,
  case when grouping(city) then 'all cities' else city end as city,
  count(*)
from warehouses
group by nation, city with rollup
order by nation is null, nation, city is null, city;
    nation city count(*)
    france paris 1
    france all cities 1
    germany berlin 1
    germany all cities 1
    italy firenze 1
    italy roma 2
    italy torino 1
    italy all cities 4
    all nations all cities 6

    If you want to suppress the full total and want the nation total before the city totals, add a HAVING clause and change the ORDER BY clause.

    select 
  nation,
  city,
  count(*)
from warehouses
group by nation, city with rollup
having nation is not null
order by nation, city is not null, city;
    nation city count(*)
    france null 1
    france paris 1
    germany null 1
    germany berlin 1
    italy null 4
    italy firenze 1
    italy roma 2
    italy torino 1

    Or with COALESCE for a single nation-or-city column:

    select
    coalesce(city, nation) as name,
    count(*)
    from warehouses
    group by nation, city with rollup
    having nation is not null
    order by nation, city is not null, city;

    name count(*)
    france 1
    paris 1
    germany 1
    berlin 1
    italy 4
    firenze 1
    roma 2
    torino 1

    Demo: https://dbfiddle.uk/4HERVYak

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search