Select all data not in Top 'n' as "Other"

Question

I hope someone can point out where I am going wrong, but I looked at this for the last 30 minutes and did not get anywhere with it.

I have a temporary table filled with data, the front-end application cannot do any logic for me, so please excuse the ugly logic of case statements in the table.

The user is happy with the result, which returns when I get the top 10 entries. Now they have decided that they want to see the group of the remaining countries (all lines are not in the top ten) as “Other”.

I tried to create a grouping of countries that are not in the top 10, but without working, I planned UNION'ing this result in the top 10 results.

SELECT c.Country, count(*) AS 'Total_Number_of_customers', COALESCE(ili.new_customers,0) AS 'New_Customers', COALESCE(ilb.existing_first,0) AS 'Existing_First_Trans', COALESCE(ilc.existing_old,0) AS 'Existing_Prev_Trans'
FROM #customer_tmp c 
LEFT JOIN (SELECT z.country, count(*) AS 'new_customers' FROM #customer_tmp z where z.customer_type='New_Customer' group by z.country)ili ON ili.country = c.country
LEFT JOIN (SELECT zy.country, count(*) AS 'existing_first' FROM #customer_tmp zy where zy.customer_type='Existing_Customer' AND zy.first_transaction=1 group by zy.country)ilb ON ilb.country = c.country
LEFT JOIN (SELECT zx.country, count(*) AS 'existing_old' FROM #customer_tmp zx where zx.customer_type='Existing_Customer' AND zx.first_transaction=0 group by zx.country)ilc ON ilc.country = c.country
GROUP BY c.country, ili.new_customers, ilb.existing_first, ilc.existing_old
ORDER BY 2 DESC

Here is the SQL that I use to get the results from my table.

For reference, each row in my temporary table contains the client identifier, the date they were created, and the type of my client, which is typical of what I'm trying to achieve.

Hope this is a simple problem and I'm just a little slow.

Thanks a lot in Adv.

Answer 1

Yes; EXCEPT, or perhaps add a line number to your query, and then select:

SELECT * FROM (

SELECT c.Country, count(*) AS 'Total_Number_of_customers', 
row_number() OVER (ORDER BY COUNT(*) DESC) AS 'r',
COALESCE(ili.new_customers,0) AS 'New_Customers', COALESCE(ilb.existing_first,0) AS 'Existing_First_Trans', COALESCE(ilc.existing_old,0) AS 'Existing_Prev_Trans'
FROM #customer_tmp c 
LEFT JOIN (SELECT z.country, count(*) AS 'new_customers' FROM #customer_tmp z where z.customer_type='New_Customer' group by z.country)ili ON ili.country = c.country
LEFT JOIN (SELECT zy.country, count(*) AS 'existing_first' FROM #customer_tmp zy where zy.customer_type='Existing_Customer' AND zy.first_transaction=1 group by zy.country)ilb ON ilb.country = c.country
LEFT JOIN (SELECT zx.country, count(*) AS 'existing_old' FROM #customer_tmp zx where zx.customer_type='Existing_Customer' AND zx.first_transaction=0 group by zx.country)ilc ON ilc.country = c.country
GROUP BY c.country, ili.new_customers, ilb.existing_first, ilc.existing_old
ORDER BY 2 DESC

) sub_query WHERE sub_query.r >= 10

This can be more flexible as you can run a single query and then easily divide the results into the top ten and the rest.

(This is equivalent to bobs answer, I think we worked on it exactly at the same time!)

Answer 2

EXCEPT SQL Server:

SELECT <fields>
FROM <table>
WHERE <conditons>
EXCEPT
<Query you want excluded>

Answer 3

(CTE)

WITH CTE AS
    (
    SELECT c.Country, count(*) AS 'Total_Number_of_customers', COALESCE(ili.new_customers,0) AS 'New_Customers', COALESCE(ilb.existing_first,0) AS 'Existing_First_Trans', COALESCE(ilc.existing_old,0) AS 'Existing_Prev_Trans'
        , ROW_NUMBER() OVER (ORDER BY count(*) DESC) AS sequence
    FROM #customer_tmp c 
    LEFT JOIN (SELECT z.country, count(*) AS 'new_customers' FROM #customer_tmp z where z.customer_type='New_Customer' group by z.country)ili ON ili.country = c.country
    LEFT JOIN (SELECT zy.country, count(*) AS 'existing_first' FROM #customer_tmp zy where zy.customer_type='Existing_Customer' AND zy.first_transaction=1 group by zy.country)ilb ON ilb.country = c.country
    LEFT JOIN (SELECT zx.country, count(*) AS 'existing_old' FROM #customer_tmp zx where zx.customer_type='Existing_Customer' AND zx.first_transaction=0 group by zx.country)ilc ON ilc.country = c.country
    GROUP BY c.country, ili.new_customers, ilb.existing_first, ilc.existing_old
    )

SELECT *
FROM CTE
WHERE sequence > 10
ORDER BY sequence

Answer 4

SELECT  country, COUNT(*) cnt, SUM(new_customer), SUM(existing_first_trans), SUM(existing_prev_trans)
FROM    (
        SELECT  CASE
                WHEN country IN
                (
                SELECT  TOP 10 country
                FROM    #customer_tmp
                ORDER BY
                        COUNT(*) DESC
                ) THEN
                        country
                ELSE    'Others'
                END AS country,
                CASE WHEN customer_type = 'New_Customer' THEN 1 END AS new_customer,
                CASE WHEN customer_type = 'Existing_Customer' AND first_transaction = 1 THEN 1 AS existing_first_trans,
                CASE WHEN customer_type = 'Existing_Customer' AND first_transaction = 0 THEN 1 AS existing_prev_trans,
        FROM    #customer_tmp
        )
GROUP BY
        country
ORDER BY
        CASE country WHEN 'Others' THEN 2 ELSE 1 END, cnt DESC