Menu
I have a MySQL table with data as:-
country | city
---------------
italy | milan
italy | rome
italy | rome
ireland | cork
uk | london
ireland | cork
I want query this and group by the country and city and have counts of both the city and the country, like this:-
country | city | city_count | country_count
---------------------------------------------
ireland | cork | 2 | 2
italy | milan | 1 | 3
italy | rome | 2 | 3
uk | london | 1 | 1
I can do:-
SELECT country, city, count(city) as city_count
FROM jobs
GROUP BY country, city
Which gives me:-
country | city | city_count
-----------------------------
ireland | cork | 2
italy | milan | 1
italy | rome | 2
uk | london | 1
Any pointer to getting the country_count too?
752
We can use the group by multiple-column technique to group multiple records into a single record. All the records with the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple-column technique.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
You can use a correlated sub-query:
SELECT country, city, count(city) as city_count,
(SELECT count(*)
FROM jobs AS j2
WHERE j1.country = j2.country) AS country_count
FROM jobs AS j1
GROUP BY country, city
Demo here
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
