CODEWITHSUNDEEP
sqlgroup-bysql-order-by
kuml
kuml

Reputation: 251

How to get count and group by country in group by sql query

I have a table with data like this:

Id | Country | Desktop_User | Mobile_User
------------------------------------------
1  | USA     | True          | False
2  | Brazil  | False         |            
3  | Mexico  |               | True
4  | India   | True          | True
5  |         | False         | True

I would like a report type data where where I can get total counts for ALL "Desktop_User" TRUE and ALL "Mobile_User" TRUE group by country (considering that some records may have NULL value for country or Desktop_User or Mobile_User column. Something like this:

Country    | Desktop_User | Mobile_User
------------------------------------------
USA        | 100          | 49
China      | 50           | 10
India      | 11           | 5
Brazil     | 11           | 0
Mexico     | 0            | 0
NO COUNTRY | 5            | 7

I would like data sorted by country in alphabetical order - is it possible

So far I have created this query (below) but I think my logic is not correct around getting all TRUES for desktop and mobile users and NULL values

And what's the best way I can sort the data?

SELECT DISTINCT Country    AS Country, 
       COUNT(Desktop_User) AS Desktop_User, 
       COUNT(Mobile_User)  AS Mobile_User
  FROM Contacts
 WHERE Desktop_User = 'True'
    OR Mobile_User  = 'True'
 GROUP BY Country

Upvotes: 1

Views: 4791

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

Try the following:

select Country AS Country
,      SUM(case when Desktop_User = 'True' then 1 else 0 end) as Desktop_User_cnt
,      SUM(case when Mobile_User = 'True' then 1 else 0 end) as Mobile_User_cnt
FROM   Contacts
WHERE  ((Desktop_User = 'True') OR (Mobile_User = 'True'))
group by Country
Order by Desktop_User_cnt desc

DISTINCT is unnecessary when you're grouping.

Upvotes: 2

Related Questions