var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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.
i tried this query. not sure whether this meets ur requirement.
Declare @vt_Table Table
insert into @vt_Table
Select 'C1', 'A1', 1500, 180, 190
Select 'C1', 'A1', 1500, 0, 900
Select 'C1', 'B2', 1800, 500, 400
Select 'C1', 'B2', 1800, 200, 200
sum (distinct total) as disttotal,
sum(used) as usedtotal,
sum(spent) as spenttotal ,
sum (distinct total)-
sum(spent) as balance
group by Customerid