"Other" category for those with 2 or less 2

[peterswan]

Hello,
I'm running a query that will be used for summary data in a chart, and I'd like to restrict the chart to only 5 or 6 values. However, the query summary results will probably be returning a few more results than this. What I'd like to do is categorize the first 5 counts as their given values, but then roll every other item up into an "Other" category.

Here's the query that returns the counts:

select
customerloc,
count(*) as loc_count
from
csq_ccp_survey_sched,
csq_customers
where
sched_cust_id = customerid
group by
customerloc
order by
loc_count desc

Is there a way to insure that everything after the first 5 entries in this query can be rolled up into on "Other" location category, with a total "Other" count?

Thanks,

Peter

 

[gmmastros]

You can add a Having clause, combined wuth Union All to achieve this. Like this...

select
      customerloc,
      count(*) as loc_count
from
      csq_ccp_survey_sched,
      csq_customers
where
     sched_cust_id = customerid
group by
      customerloc
[!]Having Count(*) > 2[/!]

Union All 

Select 'Other', Sum(loc_Count)
From   (
       select
             customerloc,
             count(*) as loc_count
       from
             csq_ccp_survey_sched,
             csq_customers
       where
            sched_cust_id = customerid
       group by
             customerloc
[!]       Having Count(*) <= 2[/!]

       ) As A

order by
      loc_count desc

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[peterswan]

Thanks, George,

That works perfect. However, I probably goofed by putting the "those with 2 or less" part in the title of my post. On second thought I think it would be better if I got the top 5 or so, and listed them, and then categorized all others into "Other".

Thanks for your help so far,

Peter

 

[gmmastros]

In that case, I suggest...

1. Create a table variable (with an identity column)
2. Insert all the data from your original select in to the table variable.
3. Pull the first 5 records from the table (based on the identity column) and then union with the sum of the rest of the data.

I suggest you give this a shot. If you run in to any problems, then post a reply back here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ESquared]

SELECT
   CustomerLocation = IsNull(X.customerloc, 'Other'),
   Cnt = count(*)
FROM
   csq_ccp_survey_sched S
   INNER JOIN csq_customers C on S.sched_cust_id = C.customerid
   LEFT JOIN (
      SELECT TOP 5 customerloc, loc_count = count(*)
      FROM
         csq_ccp_survey_sched S
         INNER JOIN csq_customers C on S.sched_cust_id = C.customerid
      GROUP BY customerloc
      ORDER BY loc_count desc
   ) X on C.customerloc = X.customerloc
GROUP BY
   IsNull(X.customerloc, 'Other')
ORDER BY
   CASE WHEN CustomerLocation = 'Other' THEN 1 ELSE 0 END,
   Cnt DESC

And do you want top 5 with ties or just the first 5? If you want with ties then you'll have to do a lot more work.

I haven't tested this so experience suggests I have probably made mistakes in the query.