CODEWITHSUNDEEP
sqloracle-database
Goran Bart Bartolić
Goran Bart Bartolić

Reputation: 81

Select all the countries that have a minimum number of clients

I have two tables, one is Countries that contains codeCountry and nameCountry and second table Client that contains privat_code_country. CodeCountry for example AUS and nameCountry Austria.

I want to select all the countries that have a minimum number of clients, in other words, I have to join this two tables and find all the countries that have minumum number of clients.

After I writed a query, I had a problem with "not a single-group group function" .

This is my query:

 select codeCountry, min(cnt) as Number_of_clients
from (select s.codeCountry, count(*) as cnt
      from countries s join
           client t
           on s.codeCountry = t.privat_code_country 
      group by s.codeCountry
     ) t order by number_of_clients desc, codeCountry asc;

I want to recive all the countries that have minimum number of clients, so what I ask, is to correct my query. TNx

Expected result is :

CodeCountry Number    umber_of_clients
AUS                    1     
CRO                    1
ITA                    1

Upvotes: 0

Views: 265

Answers (4)

sagi
sagi

Reputation: 40481

I'm not entirly following your logic, your inner query already leave you with 1 record for each codeCountry, so why do you need to choose min(count) ?

Try this:

SELECT * FROM (
    select s.codeCountry, count(*) as number_of_clients 
    from countries s join
           client t
           on s.codeCountry = t.privat_code_country 
    group by s.codeCountry) tt
WHERE tt.number_of_clients = (select min(cnt) from(SELECT count(*) as cnt FROM countries s
                           join client t
                             on s.codeCountry = t.privat_code_country 
                           group by s.codeCountry))
order by tt.codeCountry asc;

I think by the look of your data that you don't have to make a join and you can select only from client table:

SELECT * FROM (
    SELECT t.codeCountry,count(*) as number_of_clients
    FROM client t
    GROUP BY t.codeCountry) tt
WHERE tt.number_of_clients = (SELECT MIN(cnt) FROM(SELECT count(*) as cnt FROM client
                                                  GROUP BY codeCountry)
                             )

Upvotes: 2

user3275661
user3275661

Reputation: 46

If you want the list of countries which share the minimum number of clients:

select x.codeCountry, x.cnt as number_of_clients 
from
(
    select t.codeCountry, min(t.cnt) over () as min_cnt, t.cnt
    from
    (
        select s.codeCountry, count(*) as cnt
        from 
        countries s 
        join 
        client t
        on s.codeCountry = t.privat_code_country 
        group by s.codeCountry

    ) t
) x
where x.cnt=x.min_cnt
order by codeCountry asc;

Upvotes: 2

dnoeth
dnoeth

Reputation: 60482

You need a RANK:

select *
from
 (
      select s.codeCountry, count(*) as cnt, 
         rank() over (order by count(*)) as rnk -- rank based on increasing counts
      from countries s join
           client t
           on s.codeCountry = t.privat_code_country 
      group by s.codeCountry
 ) dt 
where rnk = 1

Upvotes: 2

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

Here is what I have understood.

select s.codeCountry, count(*) as cnt
  from countries s join client t
           on s.codeCountry = t.privat_code_country 
 group by s.codeCountry
 having count(1) > <the value you consider as minimum>
 order by s.codeCountry asc;

Upvotes: 0

Related Questions