Title: mysql dup count and total
Tags: mysql;sql
Question: i have a table like so,
name food
A    Apple
B    Banana
A    Banana
C    Apple
i want the distinct number of fruits and its total dup count.
food  count
2               4
SELECT food, COUNT(food) AS cnt
FROM table
GROUP BY food
HAVING (cnt &gt; 1)
gives me total for each fruit. how would you modify this query to the result i want
Comment: Could you give an example of how the result should look?


Here is another answer: SELECT count(distinct food), COUNT(*) AS cnt
from tablename

Here is another answer: Use an additional layer of aggregation:
SELECT COUNT(*) as num_foods, SUM(cnt) as num_rows
FROM (SELECT food, COUNT(food) AS cnt
FROM table
GROUP BY food
) f;

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2018:00:02/09/18', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><sql>', 'Title': 'mysql dup count and total', 'ViewCount': 212, 'id': 48708378, 'user_AboutMe': '', 'user_AccountId': 284314.0, 'user_DisplayName': 'vardha', 'user_Reputation': 384.0, 'user_Views': 99.0, 'user_id': 582349.0}}