[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-22
10
Medium Priority
?
1,316 Views
Last Modified: 2013-11-16
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 !
0
Comment
Question by:sealift_info
10 Comments
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24211802
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

0
 

Author Comment

by:sealift_info
ID: 24211827
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
0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24211867
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sealift_info
ID: 24211919
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24212032
can you provide some sample set and expected result?
0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24212167
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

0
 
LVL 7

Accepted Solution

by:
Alpha Au earned 2000 total points
ID: 24212170
Sorry, should be this,
i missed out the ordering criteria.
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 count_id 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 count(type) desc)
		) b
 
) resultatble 
order by count_id desc

Open in new window

0
 

Expert Comment

by:Prasenjit_Dutta
ID: 24214171
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 ...
0
 

Author Closing Comment

by:sealift_info
ID: 31573634
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.
0
 

Author Comment

by:sealift_info
ID: 24214702
Hi Prasenjit,
Your solution works as well and looks identical to Alphau's code. I saw your reply after having accepted the solution.

Thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What we learned in Webroot's webinar on multi-vector protection.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question