Simple Stored Proc

Question

Simple Stored Proc

kishore@xinthe.com

Grasshopper

Points: 12
More actions
October 23, 2002 at 5:58 am

#79643

I want to write a simple stored proc on Northwind database which gives results in the following format. help needed.
No of orders No of customers
------------ ---------------
0 - 105
11-2020
21-3025
Thanks
Kishore

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

Try this

SELECT cast(OrderBand *10 as varchar(3)) + '-' + cast(OrderBand * 10 + 9 as varchar(3)) "No of Orders", countOfCustomers "No of Customers"

FROM (SELECT countOfOrders % 10 OrderBand, count(*) countOfCustomers

FROM (SELECT CustomerId, count(*) countOfOrders

FROM Orders

GROUP BY customerId)as agg

GROUP BY countOfOrders % 10) as agg2

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

quote:
Try this
SELECT cast(OrderBand *10 as varchar(3)) + '-' + cast(OrderBand * 10 + 9 as varchar(3)) "No of Orders", countOfCustomers "No of Customers"
FROM (SELECT countOfOrders % 10 OrderBand, count(*) countOfCustomers
FROM (SELECT CustomerId, count(*) countOfOrders
FROM Orders
GROUP BY customerId)as agg
GROUP BY countOfOrders % 10) as agg2
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

Sorry for the blank post, possibly this might also work:

select cast(a.numberoforders%10*10 as char(3)) + '-' + cast((1+a.numberoforders%10)*10 as char(3)), count(*) from

(SELECT count(*) numberoforders, customerid from orders

group by customerid) a

group by cast(a.numberoforders%10*10 as char(3)) + '-' + cast((1+a.numberoforders%10)*10 as char(3))

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Sorry for the blank post, possibly this might also work:

select cast(a.numberoforders%10*10 as char(3)) + '-' + cast((1+a.numberoforders%10)*10 as char(3)), count(*) from

(SELECT count(*) numberoforders, customerid from orders

group by customerid) a

group by cast(a.numberoforders%10*10 as char(3)) + '-' + cast((1+a.numberoforders%10)*10 as char(3))

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

kishore@xinthe.com Grasshopper Points: 12 More actions · Answer 5

Dear Greg, Simon

Excellent. Thanks.

Please note that I changed the % to / to get the correct results.

For example, 26 orders was being grouped under 60-69 if I use %. If I use /, it will be grouped under 20-29.

Thanks again

Kishore