Grouping a Calculation - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping a Calculation

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2001-10-22 : 16:37:44

OK-

I know that crossposting or multiples posts on the same subject is frowned upon, but I think the previous question I asked has been garbled so that I'm not sure if anyone understands it anymore. I think this because any question I've had before people can answer the question as if it were trivial. I'm not sure this is much harder.

Let me try to simplify it here.

I have an application that has a table with a field shippedType and a field customerID. shippedType gives a description of a shipping methed (eg. Ground, Air...) CustomerID is the customer that placed the order. (eg. 1, 49, 192...)

I am trying to write a report that will list all of the customers and the percentage of time they use each shipping method. The final report will look something like.

CustomerID Ground Air International
3 78 12 10
94 98 0 2
.
.
.

I'm thinking that getting back a table in that format will be a little too difficult and probably unusable because I'm in effect changing entries in a field into columns. So I'm thinking I need to get back a recordset that looks like

CustomerID ShipMethod Percentage
3 Ground 78
3 Air 12
3 International 10
94 Ground 98
94 Air 0
94 International 2

From here I can manipulate the data to put it in a table however I want.

Previously I've been helped with the code that determines the percentage of each shipping method, but it does not differenciate by customer. It looks like this


SELECT orderShippingInstructions,
Round((Count(*)/(SELECT Convert(float,Count(*)) FROM tblOrders))*100,0)  AS "thePercent" 
FROM tblOrders GROUP BY orderShippingInstructions

I've tried adding some group by customerID statements in various places. I can get it grouped, but the percentages were as if each customer placed every order. I can't seem to weed it out.

Again sorry for reposting this, but I hope it clarifies some of what I said before!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources