SQL Issue - 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

General SQL Server Forums

New to SQL Server Programming

SQL Issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-12-22 : 00:59:19

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

My select statement should use the total for all courses and deduct Used and Spent. However, it should only take one total for same course but need to deduct the spent and used.

For example,
For Customer C1, he attended two course which is A1 and B2. So the total should be take a total of 1500+1800=3300 and the total should deduct for all Used and Spent which are 3300 - (180+190+900+500+400+200+200)=730.

How can I write my select statement in order to achieve this result?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-22 : 01:32:56

SELECT CUSTOMERID,
COURSE,
SUM(SPENT)AS SPENT,
SUM(USED)AS USED,
SUM(TOTAL)AS TOTAL
INTO #TEST
FROM CUST
GROUP BY CUSTOMERID,
COURSE
SELECT TOTAL-SPENT-USED AS GRAND_TOTAL FROM #TEST
DROP TABLE #TEST
This might work....

Sachin.Nand

2937 Posts

Posted - 2010-12-22 : 01:34:00

Something like this

Sum(Course)-(Sum(Total)+Sum(Spent)) group by Customer

PBUH

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-22 : 01:41:46

quote:
Originally posted by Sachin.Nand

Something like this
Sum(Course)-(Sum(Total)+Sum(Spent)) group by Customer

PBUH

hi sachin your way is very optimized...great

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-12-22 : 01:58:19

Hi,

The statement seems does not work. The result I get is not correct.

For PBUH statement, course is varchar,it can't use to SUM...

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

For the result I should get is as the following:

CustomerID Total Used Spent Balance
C1 3300 880 1690 730

Where Total is get distinct total for a course.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-22 : 02:18:09

u just need to change the columns as per ur requirement.the logic is perfect.
select Sum(TOTAL)-(Sum(USED)+Sum(Spent)) from cust group by COURSE
check this one.

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-12-22 : 02:35:34

Hi,

The problem is the sql statement will Sum up total for same course. If same course, it should only take one total.

For example, for Course A1, there are two same course with each total 1500. What I need is just get 1500 if same course but not 3000.

I am facing problem on this part.

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-12-22 : 03:07:45

Hi Bohra, you are right. But if I use group by Course, there will be two results.

CustomerID Course Total Used Spent Balance
C1 A1 1500 180 1090 230
C1 B2 1800 700 600 500

How can I group all together to make it something like:
CustomerID Total Used Spent Balance
C1 3300 880 1690 730

I know that it can be done by insert the data into temp table and then retrieve from temp table. But I am trying to avoid creating new table.

I know how to get it

select Sum(temp.Total) From(
Select (Max(TOTALPaid_Amt)-(Sum(Utilized_Treatment_Amt))) as Total from customer.cvwCustomerPackage where customer_id='BJ10001' group by package_id) as temp

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-22 : 03:19:49

Try:

SELECT C.CUSTOMERID,SUM(SUBTAB.TOTAL)- (SUM(C.Used) + Sum(C.Spent))
from Customer C
Inner join
(Select T.CustomerID,T.Course,Max(T.Total) TOTAL from Customer T
group by T.CustomerID,T.Course) as SubTab
ON C.CustomerID = SubTab.CustomerID and C.Course = SubTab.Course
group by c.cUSTOMERID

I have not carried out testing.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-22 : 03:49:58

The earlier reply may not give correct answer. Try this:

SELECT SubTab.CUSTOMERID,SUM(SUBTAB.TOTAL)-(Max(E.Used) + Max(E.Spent))
from
(Select T.CustomerID,T.Course,Max(T.Total) TOTAL from Customer T
group by T.CustomerID,T.Course) as SubTab
Inner join
(Select X.CustomerID,SUM(X.Used) Used, SUM(X.Spent) Spent from Customer X
group by X.CustomerID) as E
ON SubTab.CustomerID = E.CustomerId
Group By SubTab.CustomerId

There must be more simple way of doing it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources