Results 1 to 2 of 2

Thread: SQL Issue

  1. #1
    Join Date
    Apr 2009

    Unanswered: SQL Issue

    Hi, I have a problem here. The following is my table data.

    CustomerID Course Total Used Spent
    C1 A1 1500 180 190
    C1 A1 1500 0 900
    C1 B2 1800 500 400
    C1 B2 1800 200 200

    What I need to get is as the following:

    CustomerID Total Used Spent Balance
    C1 3300 880 1690 730

    Where 3300 is get from two course distinct total which is 1500+3300. Used is get from all Used data and Spent is get from all Spent data. The balance should be Total-Used-Spent.

    How should I write my sql so that it will determine if there are same course with different used and spent, then i should only get a total but not two duplicate total?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Bangalore, India
    i tried this query. not sure whether this meets ur requirement.

    Declare @vt_Table Table
    customerid varchar(2),
    course varchar(2),
    total int,
    used int,
    spent int

    insert into @vt_Table
    customerid ,
    course ,
    total ,
    used ,
    Select 'C1', 'A1', 1500, 180, 190
    Select 'C1', 'A1', 1500, 0, 900
    Select 'C1', 'B2', 1800, 500, 400
    Select 'C1', 'B2', 1800, 200, 200

    Select Customerid,
    sum (distinct total) as disttotal,
    sum(used) as usedtotal,
    sum(spent) as spenttotal ,
    sum (distinct total)-
    sum(used) -
    sum(spent) as balance
    from @vt_Table
    group by Customerid


Posting Permissions

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