CODEWITHSUNDEEP
mysqlcountdistinct
G Delaney
G Delaney

Reputation: 15

Using Distinct, Count and Group together in MySQL

I have a database table of 'attractions' with columns for both the county and/or city for each attraction. I need to display a list of counties and cities together, alphabetically, with the total number of records for each county or city.

The following SQL query works fine except the COUNT is displaying a total of only one for each instead of the total number of attractions in each county or city.

Could anyone tell me how to combine the two SQL statements below together?

SQL:

   SELECT entry, COUNT(entry) AS totalForEntry 
   FROM ( SELECT DISTINCT county AS entry
          FROM venues 
          WHERE (county  IS NOT NULL AND county <> '' ) 

          UNION ALL 

          SELECT DISTINCT city as entry
          FROM venues
          WHERE (city IS NOT NULL 
                 AND
                 city  <> ''  
                 )
      ) X GROUP BY entry ORDER BY entry ASC

Results:

Hove (1)
Inverness-shire (1)
Isle of Man (1)
Kent (1)
Lancashire (1)
Leeds (1)
Leicester (1)
Leicestershire (2)
Lincolnshire (1)
Liverpool (1)

The following works for counting the counties, I need it to count the cities too and combine it with the query above:

SELECT DISTINCT county, COUNT(county) AS Count
FROM venues
WHERE (county IS NOT NULL AND county <> '') 
GROUP BY county ORDER BY county ASC

The correct result should be something like:

Hove (7)
Inverness-shire (3)
Isle of Man (12)
Kent (20)
Lancashire (34)
Leeds (31)
Leicester (5)
Leicestershire (53)
Lincolnshire (7)
Liverpool (43)

Many thanks in advance.

Upvotes: 1

Views: 1461

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

It doesn't make sense to use DISTINCT with GROUP BY. You can do:

SELECT county AS entry, COUNT(county) AS Count
FROM venues
WHERE (county IS NOT NULL AND county <> '') 
GROUP BY county 

UNION ALL 

SELECT city as entry, COUNT(city) As Count
FROM venues
WHERE (city IS NOT NULL AND city  <> '')
GROUP BY city 
ORDER BY entry ASC

Upvotes: 1

Related Questions