Divide Subtotal by Total - 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)

Divide Subtotal by Total

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-01 : 23:55:48

Hiya!
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?

Thanks,
Sarah

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 00:21:08

Using Derive Query this way should help you.

select customerkey,sum(paid)/(select sum(paid) from orders)
from orders
group by customerkey

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-02 : 12:43:21

Hi,
Thanks for your reply. That's exactly what I'm doing,and that's not the problem. Here's my query:
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 Source
The 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?

Thanks,
Sarah

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-03 : 00:36:48

Hey. WRT your comment in your other post about having received "no good response", umm sometimes peoples' responses are as good as they can be given the info they have to work with. Thanks to Nazim's comments and your additional info following that, I'll try to help you out given what I think I'm seeing...

If you're using multiple levels of joining in the same tables with the same where clauses, I think using a derived table would be a good idea, for several reasons. One is that you don't want to copy and paste code if you don't have to, so a derived table lets you only maintain your complex joins in one place. Plus, you don't have to rely on SQL server's intelligence to somehow figure out that it's the same join and auto-optimize (I don't even know if it would do this), but you've already optimized it by using a derived table and just referencing that table twice.

If that's not what you were talking about, then let's all try again...

Edited by - aclarke on 01/03/2002 00:41:22

Nazim
A custom title

1408 Posts

Posted - 2002-01-03 : 01:12:03

Hi Clarke,

We always cross our paths

.

you can always use your primary key to link your outer table with your inner derived table

to give u an idea
select customerkey,sum(paid)/(select sum(paid) from orders k where o.customerkey=k.customerkey)
from orders o
group by customerkey

i think this should give u some idea to refine your query, and moreever follow my good friend Clarke's Advice

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/03/2002 01:24:35

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-03 : 01:21:53

quote:

select customerkey,sum(paid)/(select sum(paid) from orders k where o.customerkey=o.customerkey)

Maybe where o.customerkey=K.customerkey)

Nazim
A custom title

1408 Posts

Posted - 2002-01-03 : 01:27:06

i just EDITED it, i pass u the job of being a good Editor Clarke. I think Some Complacent virus has come into me. this is the third time i have done something like that

.

know of any such Virus Cleaner Clarke

.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-03 : 01:33:51

quote:

know of any such Virus Cleaner Clarke.

Coffee?

What time is it in India/UAE anyways (I noticed you changed your country a few days ago)? It's night where I am (22:30, I like working at night and playing during the day) so it must be early morning for you.

Nazim
A custom title

1408 Posts

Posted - 2002-01-03 : 01:37:52

quote:

What time is it in India/UAE anyways (I noticed you changed your country a few days ago)? It's night where I am (22:30, I like working at night and playing during the day) so it must be early morning for you.

Actually am an indian presently working in United Arab Emirates. Am in two minds which country i should keep in my profile, might be you help

. rite now its 10:33 A.M. Looks you are the boss at your office. As for me i believe in "Early to Bed , Early to Rise"

.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/03/2002 01:44:23

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-03 : 01:45:14

I'm early to rise, get to work, go surfing/mountain biking/walk the dog, run errands, go back to work, late to bed :-) especially while my wife's not here :-(

All of which I'm sure simondeutch/sarah is INTENSELY interested in

Nazim
A custom title

1408 Posts

Posted - 2002-01-03 : 02:00:33

Will Wind up this here only ,otherwise Sarah will kill us. if not Sarah, Graz will

.

btw , Sarah is your problem solved?

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-03 : 07:54:41

I think it's true to say that if you're trying to calculate a value based on two or more different levels of aggregation, you will have to separately reference the set of rows you want for each level of aggregation. There's no equivalent to the LET/WHERE in functional languages, unless you count views, and they don't have the same scoping or lifetime.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-03 : 12:34:47

Hi!
Sorry, AClarke, I didn't mean to criticize Nazim's initial reply, I meant that I didn't get many responses.
I haven't solved it yet. Every solution I try gives different error messages! I'm actually quite new to this stuff. To make myself very clear, I won't simplify the problem, I'll post it at length.
I'm dealing with 3 tables mainly, Payments, Vouchers, and VouchersDetails, for a medical system. I'm trying to run a report to show Total Paid, Total Charges, Total Adjusted, Number of Visits,Number of Procedures, Percentage of Total Visits, Percentage of Total Paid for each ins. company. There are two possibities:
1. An Insurance has Charges and Payments = normal. (rows in all tables)
2. An Insurance has Payments,but no corresponding Charges. This makes sense in a medical system where secondary insurance pays without being billed directly.
Here's some DDL:
CREATE TABLE Payments (Rowid int identity(1,1)Primary key,VouchNo INT,LineNumber Tinyint,Source CHAR(10),AmtPaid Money,AdjustAmt Money)
CREATE TABLE Vouchersdetails (VouchNo INT,LineNumber TinyInt,ProcCode Char(7),Units tinyint,Charge Money,DateOfService DATETIME,CONSTRAINT PRIMARY KEY(VouchNo,LineNumber))
CREATE TABLE Vouchers (VouchNo INT PRIMARY KEY,DrCode CHAR(5),InsCo CHAR(10))
INSERT INTO Vouchers VALUES(10,'AA','Comp1')
INSERT INTO Vouchers VALUES(20,'AA','Comp1')
INSERT INTO Vouchers VALUES(30,'BB','Comp1')
INSERT INTO vouchers VALUES(40,'BB','Comp2')
INSERT INTO Vouchers VALUES(50,'BB','Comp3')

INSERT INTO VouchersDetails VALUES(10,1,'XRay',2,100,'1/1/2001')
INSERT INTO VouchersDetails VALUES(10,2,'EKG',1,150,'1/1/2001')
INSERT INTO VouchersDetails VALUES(20,1,'XRay',1,50,'1/10/2001')
INSERT INTO VouchersDetails VALUES(30,1,'EMG',1,200,'1/1/2001')
INSERT INTO VouchersDetails VALUES(30,2,'MRI',2,500,'1/1/2001')
INSERT INTO VouchersDetails VALUES(40,1,'CTScan',1,350,'1/1/2001')
INSERT INTO VouchersDetails VALUES(50,1,'CTScan',1,350,'1/1/2001')

INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(10,1,'Comp2',50,25)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(10,1,'Comp4',25,0)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(10,2,'Comp1',120,30)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(20,1,'Comp1',25,10)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(20,1,'Comp5',15,0)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(40,1,'Comp2',250,0)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(40,1,'Comp1',100,0)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(50,1,'Comp3',175,0)
INSERT INTO Payments(VouchNo,LineNumber,Source,AmtPaid,AdjustAmt)VALUES(50,1,'Comp4',175,0)

And the user has a choice of date range for the service date, only specific doctors, specific insurance companies, and specific procedures.

Any help would be Greatly Appreciated!

Sarah Berger MCSD

aclarke
Posting Yak Master

133 Posts

Posted - 2002-01-04 : 01:33:02

I was thinking about something along these lines. You could just add whatever constraints you want to #hmm.


create table #hmm (source char(10), amtPaid money, adjusted money)

insert into #hmm

SELECT M.Source,SUM(M.AmtPaid) AS Paid,SUM(M.AdjustAmt) AS Adjusted

FROM Payments M
	left JOIN VouchersDetails VD ON VD.VouchNo = M.VouchNo AND M.Source IN(SELECT 'Patient' AS Code) AND VD.LineNumber = M.LineNumber
		AND VD.DateOfService BETWEEN '1/1/1990' AND '11/28/2005' AND VD.ProcCode BETWEEN '0' AND '9999999'
	left JOIN Vouchers V ON V.VouchNo = VD.VouchNo 

GROUP BY Source
ORDER BY Source


select source, amtPaid, adjusted, amtPaid/ (select sum(amtPaid) from #hmm) as pctPaid
from #hmm

drop table #hmm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources