CODEWITHSUNDEEP
mysqlsql
arie
arie

Reputation: 11

How to Count Distinct with Group By and the entire Column

I'm having a hard time wording what I need/wording the search result, so apologies if this is a stupid question/has been answered before. I'm trying to write a query in SQL for a table such as below: 

Country    Unique_ID   
US         123
US         124
UK         125
Australia  126

That will output the follow table: 

Country    Count_Distinct   
US         2
UK         1
Australia  1
All        4

I know I can select the countryid and count distinct the country codes, and I know I can just count distinct the countryid codes to get the "All" number. I can't figure out how to write a query to get the follow output that's not two separate queries.

If you need information or clarification please let me know. Thanks!

Upvotes: 1

Views: 59

Answers (2)

Leo
Leo

Reputation: 898

Can you please try this :

select country,count(distinct unique_id) as count distinct from table group by rollup(country)

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Use WITH ROLLUP:

select Country, count(distinct Unique_ID) Count_Distinct
from mytable
group by Country
with rollup

If you want the text "All" (you get a null for the country by default), wrap it in another query to change the null to "All":

select coalesce(Country, "All") Country, Count_Distinct
from (
    select Country, count(distinct Unique_ID) Count_Distinct
    from mytable
    group by Country
    with rollup
) x

Upvotes: 4

Related Questions