Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Lightbulb 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
    add constraint fk_contractdiscount_contractnum
    foreign key (contractNum)
    references contract(contractNum)
    ;

    alter table contractDiscount
    add constraint fk_contractdiscount_discountnum
    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. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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