CODEWITHSUNDEEP
sqlsql-server
P. MAJ
P. MAJ

Reputation: 159

Group By does not take into account the changes

first off, I apologize if this was asked before.

I got a query which returns the name of the country versus the bookings made in the last year.

however, the output requirement is to have the 4 countries within the UK to be all classed as "UK".

my query is:

select distinct

    [Exiting Country] = case
        when co.COUNTRY_NM in ('ENGLAND', 'N. IRELAND', 'SCOTLAND', 'WALES', 'UNITED KINGDOM')
            then 'UK'
        else co.COUNTRY_NM
    end
    ,count(bk.booking_id) as [Number of Bookings]

from dbo.BOOKINGS as bk
left join dbo.COUNTRY as co
    on bk.COUNTRY_ID = co.COUNTRY_ID

group by co.COUNTRY_NM

order by [Exiting Country] asc

however the result will be as:

enter image description here

how can i have it to group it by the modified country name instead? any simple way to do it?

thanks!

Upvotes: 1

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You need to repeat the case expression in the group by:

select (case when co.COUNTRY_NM in ('ENGLAND', 'N. IRELAND', 'SCOTLAND', 'WALES', 'UNITED KINGDOM')
            then 'UK'
            else co.COUNTRY_NM
        end) as country,
       count(bk.booking_id) as num_bookings
from dbo.COUNTRY co left join
     dbo.BOOKINGS bk
     on bk.COUNTRY_ID = co.COUNTRY_ID
group by (case when co.COUNTRY_NM in ('ENGLAND', 'N. IRELAND', 'SCOTLAND', 'WALES', 'UNITED KINGDOM')
               then 'UK'
               else co.COUNTRY_NM
          end)
order by country asc;

Note the other changes to the query:

  • The column names are simplified. Try to avoid situations where you need to escape the name.
  • The left join is inverted. Presumably, you want to keep all countries, even when there are no matches.

Some databases allow you to use the column alias in the group by as well as the order by; alas, SQL Server is not one of those.

EDIT:

You can phrase this as:

select v.country,
       count(bk.booking_id) as num_bookings
from dbo.COUNTRY co left join
     dbo.BOOKINGS bk
     on bk.COUNTRY_ID = co.COUNTRY_ID cross apply
     (values (case when co.COUNTRY_NM in ('ENGLAND', 'N. IRELAND', 'SCOTLAND', 'WALES', 'UNITED KINGDOM')
                   then 'UK'
                   else co.COUNTRY_NM
               end)
     ) v(country)
group by v.country
order by v.country asc;

Upvotes: 2

Related Questions