Link to home
Start Free TrialLog in
Avatar of sealift_info
sealift_info

asked on

How to select top n records & sum remaining records in a single line - For use in Piechart

Hi,
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 !
Avatar of Alpha Au
Alpha Au
Flag of Hong Kong image

You may try the following
select customer_name,count_id from (
 
	select top 10 customer_name customer_name, count(id) as count_id 
	from db_orders group by customer_name      
	order by count(id) desc ) as customer_name 
	
union all
 
	select 'Other' as customer_name,count(id) as count_id 
	from db_orders
	where customer_name not in
	(select top 10 customer_name from db_orders group by  customer_name order by count(id) desc)
 
order by count_id desc

Open in new window

Avatar of sealift_info
sealift_info

ASKER

Hi Alphaau,
Is there any difference between your code and the non-functionning code I submitted ? I'm not too good at sql-server and need the piechart component to be ready by friday ....

Thanks
The different is bracketing. (i.e. grouping the data)

you may follow the code snippet, which i separate the select statement more clear.

the query use sysobjects, which exists in every db in ms sql server,  you may fill in your table name and field after tested
select * from (
		select name,count_id from 
			(select top 10 name , count(name) as count_id 
				from sysobjects 
				group by name      
				order by name desc ) a
union all
        select name,count_id from (
			select 'Other' as name,count(name) as count_id 
			from sysobjects
			where name not in
			(select top 10 name from sysobjects group by name order by name desc)
		) b
 
) resultatble 
order by count_id desc

Open in new window

Thanks for your fast reply.
Your code works with my table but does not do what I want.

I need to get the 10 highest order counts grouped by customer (name), plus the additional remaining order count.

Your code displays the top 10 [name] without taking into account their count value. This may work considering there is only one occurrence per name (like in your example with sysobjects table). But in my case i have distinct values of order counts for each customer

Just think about a piechart. It usually shows the n slices with the highest occurrence (25%,30%,15%,..) and sums up the rest in an 'Other' slice.

Hope being clear enough this time.
Avatar of Sharath S
can you provide some sample set and expected result?
So, how about this?

you can have the percentage like following,

you will have to replace suitable table name and field name ^__^
select type, count_id, count_total, cast( count_id as float) * 100 /count_total from (
		select type,count_id, (select count(type) from sysobjects) count_total  from 
			(select top 10 type , count(type) as count_id 
				from sysobjects 
				group by type      
				order by type desc ) a
union all
        select type,count_id , (select count(type) from sysobjects) count_total from (
			select 'Other' as type,count(type) as count_id 
			from sysobjects
			where type not in
			(select top 10 type from sysobjects group by type order by type desc)
		) b
 
) resultatble 
order by count_id desc

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Alpha Au
Alpha Au
Flag of Hong Kong image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi, your logic and code both are ok, except the last line which is throwing error here i.e.

select top 10 customer_name from db_orders group by  customer_name order by count(id) desc)

Although, individually it runs fine but with Union all ite does not work. I have a slightly modified code .. check it

select customer_Name,Order_Count from (select top 10 Customer_Name,count(order_No) as Order_Count
      from db_orders(nolock) group by customer_name order by order_count desc) as CustomerA
            union all
select 'Others' as Customer_Name,count(order_no) as Order_Count from db_orders(nolock)
      where Customer_Name not in ( select Customer_Name from
      (select top 10 Customer_Name,count(order_No) as Order_Count
      from db_orders(nolock) group by customer_name order by order_count desc) as CustomerB)



Hope, it satisfy ur needs ...
Thanks Alphaau, this is it.
Its actually a quiet useful piece of sql for all those who need to create a data source for their piechart. This query selects the top n rows in a table sorted by occurrence of (column) and sums the remaining rows into one single 'Others' row. Classic.
Hi Prasenjit,
Your solution works as well and looks identical to Alphau's code. I saw your reply after having accepted the solution.

Thanks