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 !
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 !
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
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
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
ASKER
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.
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.
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 ^__^
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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_
from db_orders(nolock) group by customer_name order by order_count desc) as CustomerA
union all
select 'Others' as Customer_Name,count(order_
where Customer_Name not in ( select Customer_Name from
(select top 10 Customer_Name,count(order_
from db_orders(nolock) group by customer_name order by order_count desc) as CustomerB)
Hope, it satisfy ur needs ...
ASKER
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.
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.
ASKER
Hi Prasenjit,
Your solution works as well and looks identical to Alphau's code. I saw your reply after having accepted the solution.
Thanks
Your solution works as well and looks identical to Alphau's code. I saw your reply after having accepted the solution.
Thanks
Open in new window