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 |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-01-02 : 23:36:26
|
Hi!I posted this topic "Divide Subtotal By Total", but with no good response. Please see if you can figure it out. I've already lost quite a bit of work over it.Thanks in advance. quote:
How do I do something like this:If I have a resultset listing all my customers and how much money I received from each, calculate what percentage out of the total for all customers was received for each particular customer, if my SELECT has a very extensive where clause. I probably need to do a SUM(Paid) / (SELECT SUM(Paid) FROM Orders) but instead of this subquery which has no where clause, should I use a derived table or put my long WHERE clause into the subquery?
And then this: quote:
SELECT M.Source,SUM(M.AmtPaid) AS Paid,SUM(M.AdjAmount) AS Adjusted,SUM(M.AmtPaid) / (SELECT SUM(AmtPaid) FROM Payments) AS PctPaid FROM Payments M INNER JOIN (VouchersDetail VD INNER JOIN Vouchers V ON V.VoucherNo = VD.Voucher AND V.DrCode IN(SELECT Code FROM Doctors))ON VD.Voucher = M.VouchNo AND M.Source IN(SELECT 'Patient' AS Code UNION SELECT Code FROM InsCompanies) AND VD.LineNumber = M.LineNumber AND VD.ServiceDate BETWEEN '1/1/1990' AND '11/28/2001' AND VD.ProcCode BETWEEN '0' AND '9999999' GROUP BY Source ORDER BY SourceThe subquery in question is the (SELECT SUM(AmtPaid) FROM Payments) which will return the sum of all rows in the table disregarding my outer WHERE clause, making the percentage paid incorrect i.e. instead of getting a total of 100% for all groups, I will get less.So my question still is: should I use a derived table with a long WHERE clause or a subquery with a long WHERE clause or is there a better way?
|
|
|
Nazim
A custom title
1408 Posts |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-03 : 03:10:43
|
| Nazim is right. If you are not happy that your question is resolved, post in that thread. Aclarke and Nazim gave some good advice in that thread that you did not respond to.Do not start another thread, when someone is involved in a thread they keep an eye on it. What you did is pretty rude IMO.As for losing quite a bit of work over it, well, really that is your problem. The people here are not paid for the help they give you and are not responsible for your work.Another thing, for more complex stuff, giving us some DDL and DML so we can create tables and insert some dummy data goes a long way. If I can paste a script into my query analyzer to set up data I am much happier to write some SQL for people. But if I have to guess at it and write all the data myself I generally won't bother unless the question really interests me.So.....be nice, polite and make it easy for people to help you and you will get a LOT further.For some further reading on this topic, check this : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11708Damian |
 |
|
|
|
|
|
|
|
Urgent! This query is bugging me!