# Thread: calculating whole school total tuition

1. Registered User
Join Date
Apr 2004
Posts
19

## Unanswered: calculating whole school total tuition

I am trying to calculate a whole school total tuition. I've got helped with individual tuition total which is

create proc sp_individualtuition
as
select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition
from contract c, contractDiscount cd, discount d
where c.contractNum = cd.contractNum
and cd.discountNum =d.discountNum
group by c.contractNum, c.tuition

however how can i calculate the whole school total tuition (adding all the individual total tuition)?

my contract, contractDiscount and discount DDL are down below

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

create table contractDiscount(
contractNum int not null,
discountNum char(3) not null
);

alter table contractDiscount
add constraint pk_contractdiscount primary key clustered (contractNum, discountNum)
;

alter table contractDiscount
foreign key (contractNum)
references contract(contractNum)
;

alter table contractDiscount
foreign key (discountNum)
references discount(discountNum)

create table discount(
discountNum char(3) primary key,
discountDesc varchar(100) not null,
discountPer decimal(3,2) not null
);

thanks
gazawaymy

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
The most obvious method is to wrap your individualtuition logic in a subquery:

create proc sp_totaltuition
as
select sum(totaltuition)
from
(select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition
from contract c, contractDiscount cd, discount d
where c.contractNum = cd.contractNum
and cd.discountNum =d.discountNum
group by c.contractNum, c.tuition) Subquery

...But code like a pro and juse JOINs instead of WHERE clauses to link tables, and do a favor the poor sot who has to read your code a year from now and drop the non-descriptive aliases:

create proc sp_totaltuition
as
select sum(totaltuition)
from
(select contract.contractNum, (contract.tuition-((sum(discount.discountPer))* contract.tuition)) as totaltuition
from contract,
inner join contractDiscount on contractDiscount.contractNum = contract.contractNum
inner join discount on discount.discountNum = contractDiscount.discountNum
group by contract.contractNum, contract.tuition) Subquery

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•