Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2006-11-27 : 14:25:12
|
| I apologize if I'm not clear on this but I greatly appreciate any help you can provide as I'm running very low on time before this report is due....How would I go about appending a column to the end of my query for the count of the total unique rows returned split by inividual sales reps? I need to create a query to find the orders details of our sales team. Basically, I need to find each individual sales person, the items they've ordered all month, and details of the ordered item. I can handle this portion with no problem.The portion I'm having trouble with is finding the total # of unique orderID's for each sales person (in other words, our sales rep "John Smith", may have 6 orders for the month and 3 items per order. This would return 18 rows for him. My problem however, is I also need to append his total orders to a final column, so in this case, I would append 6).So my query looks something like the following:-------------------------------------------------SELECT o.orders_firstname as repFirstName, o.orders_MiddleInit as repMiddleInit, o.orders_LastName as repLastName, p.prduct_description as description, p.prduct_partnumber as mktNumber, o.ORDERS_Address1 as repAddress, COUNT(DISTINCT o.orderID) as totalOrdersFROM ORDERS o INNER JOIN table2 os on os.fk1 = o.fk1WHERE os.DateShipped >= @MonthStartDate AND os.DateShipped <= @MonthEndDate------------------------------------------------------------I've obviously left out some of the details but my problem line is the line beginning with "COUNT".The data I need to have returned looks something like this, with "totalOrders" being the count of all unique orderID's for that particular rep:--------------------------------------------------------------repFirstName | repMiddleInit | repLastName | mktNumber | totalOrders-------------------------------------------------------------------- John | A. | Smith | J8837-T | 6 John | A. | Smith | RTHFY-T | 6 John | A. | Smith | RTHFY-T | 6 John | A. | Smith | YRDKD-B | 6 John | A. | Smith | GHEES-Z | 6 John | A. | Smith | YYUPO-Q | 6 John | A. | Smith | CWCSW-M | 6 John | A. | Smith | RTHFY-T | 6 John | A. | Smith | RTHFY-T | 6 Amy | B. | Andrews | J8837-T | 2 Amy | B. | Andrews | RTHFY-T | 2 Amy | B. | Andrews | YYUPO-Q | 2 Amy | B. | Andrews | RTHFY-T | 2 Amy | B. | Andrews | CWCSW-M | 2-------------------------------------------------------------------I'd appreciate any help you can provide me with. Thanks in advance.-Goalie35 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-27 : 22:05:58
|
| [code]SELECT o.orders_firstname as repFirstName, o.orders_MiddleInit as repMiddleInit, o.orders_LastName as repLastName, p.prduct_description as description, p.prduct_partnumber as mktNumber, o.ORDERS_Address1 as repAddress, COUNT(DISTINCT o.orderID) as totalOrdersFROM ORDERS o JOIN table2 os on os.fk1 = o.fk1 Join Product p on o.product_id = p.product_idWHERE os.DateShipped >= @MonthStartDate AND os.DateShipped <= @MonthEndDategroup by o.orders_firstname o.orders_MiddleInit o.orders_LastName p.prduct_description p.prduct_partnumber o.ORDERS_Address1[/code]???There are many missing points in your post. What is Table2? Why is it used? Why you are storing representative details in the orders table?There is no table with alias p (i believe it's product table).If you can post complete table structures, it would be more helpful.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
|
|
|
|
|
Returning a count of orders per sales person?