Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

simple addition

Status
Not open for further replies.

meltingpot

Technical User
May 11, 2004
118
GB
Hi chaps

Im trying to solve a simple maths problem

example
10(Quals)+6(Crew)-4(statuscode)=12(result)

Using the following SQL Query

SELECT (Courselist.quals+Courselist.crew)-Applications.StatusCode

FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID

WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

Note: Courselist.CourseID is posted from Request.QueryString on an ASP web page.

Im having no luck, can anybody help :)


 
Try this:

Code:
SELECT (SUM(Courselist.quals)+ SUM(Courselist.quals)) - COUNT(DISTINCT Applications.StatusCode)
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

-SQLBill

Posting advice: FAQ481-4875
 
I found an error in my code...sorry. Try this and let us know what it returns:
Code:
SELECT SUM(Courselist.quals) AS 'Quals',
       SUM(Courselist.crew) AS 'Crew',
       COUNT(DISTINCT Applications.StatusCode) AS 'Taken',
(SUM(Courselist.quals)+ SUM(Courselist.quals)) - COUNT(DISTINCT Applications.StatusCode) AS 'Left'
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

Sorry about not noticing the duplicated Courselist.quals in my previous scripts.

-SQLBill

Posting advice: FAQ481-4875
 
cheers ...

I get the following error..

Syntax error (missing operator) in query expression '(SUM(Courselist.quals)+ SUM(Courselist.quals)) - COUNT(DISTINCT Applications.StatusCode)'.
 
I notice a couple things here.

Code:
SELECT (SUM(Courselist.[!]quals[/!])+ SUM(Courselist.[!]quals[/!])) - COUNT(Applications.StatusCode)
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

Your adding the same column twice. That second one should be crews.

Second problem, you are summing the date from CourseList, which is wrong, because you are effectively returning that info for each corresponding row in the status code column. So... if there are 5 records in applications, your summing the quals and crews 5 times.

This should do the trick.

Code:
SELECT Courselist.quals + Courselist.crew - COUNT(Applications.StatusCode)
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

You don't need the parenthesis because the order of operations does not matter.

To analyze your previous results...

qulas= 10 (seperate query)
crew = 6 (seperate query)
Taken=5 (correct from seperate query)
Left= 95 (way wrong)

Quals + Quals = 20

Since you are Summing the values (over the records) and there are 5 records, you get 20 + 20 + 20 + 20 + 20 = 100.

subtract 5 (for the count). Your result is 95.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Sorry...this is a really bad day for me...I noticed my code error, told you I caught it and then never corrected it.

Code:
SELECT SUM(Courselist.quals) AS 'Quals',
       SUM(Courselist.crew) AS 'Crew',
       COUNT(DISTINCT Applications.StatusCode) AS 'Taken',
(SUM(Courselist.quals)+ SUM(Courselist.[COLOR=red]crew[/color red])) - COUNT(DISTINCT Applications.StatusCode) AS 'Left'
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

I tested the "COUNT(DISTINCT" and that will work.

-SQLBill

Posting advice: FAQ481-4875
 
cheers gmmastros.

ive tried you statement but get the following error
You tried to execute a query that does not include the specified expression 'Courselist.quals+Courselist.crew-COUNT(Applications.StatusCode)' as part of an aggregate function.

I did get the repeat sum of the quals ..
 
SQLBILL: Im not sure I can us the 'AS' . The output in on a ASP webpage ...I did try it but got the following error..

Syntax error (missing operator) in query expression 'COUNT(DISTINCT Applications.StatusCode)'.

I have to go offline now, but thanks everybody for all your help !

Back tomorrow

:)
 
Try this one....

Code:
Select CourseList.quals + CourseList.Crew - IsNull(A.Taken, 0)
From   CourseList
       Left  Join (
         Select Applications.CourseId, 
                Count(Applications.StatusCode) As Taken
         From   Applications
         Where  Applictions.StatusCode = 'Accepted'
         Group BY Applications.CourseId
         ) As A
         On CourseList.CourseId = A.CourseId
Where  CourseList.CourseId = ID


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Hi gmmastros

I get the following errorusing the code above:

Wrong number of arguments used with function in query expression 'CourseList.quals + CourseList.Crew - IsNull(A.Taken, 0)'.
:)
 
What database are you using? This is a SQL Server database, right?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top