How to select top n records & sum remaining records in a single line - For use in Piechart
Posted on 2009-04-22
I want to create an sql query that counts the number of orders for each customer from my table db_orders. Column headers for this query are customer_name and count_id (order count for each customer). Since this query is to be used by a piechart I only want the first 10 records with the highest order counts to appear. I also want this query to display an 11th line showing the total order count for the remaining customers.
The best way I found to achieve this is as follows :
1: select customer_name,count_id
2: from (
3: select top 10 customer_name,count(id) as count_id
4: from db_orders group by customer_name
5: order by count(id) desc ) as customer_name
6: union all
7: select customer_name,count_id
8: from (
9: select 'Other' as customer_name,count(id) as count_id from db_orders
10: where customer_name not in
11: (select top 10 customer_name from db_orders group by customer_name order by count(id) desc)
12: ) as customer_name order by count_id desc
The use of count(id) in the subquery at line 11 raises following error : ORDER BY items must appear in the select list if the statement contains a UNION operator.
Any ideas on how to achieve the above ?
Please help !