simple addition

[meltingpot]

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

 

[monksnake]

Are you getting a convert error?

 

[gmmastros]

I see one problem...

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'

At first, your treating status code as a number and then later you are treating it like a string ( = 'Accepted' ).



-George

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

 

[meltingpot]

I just get a '0' returned on the ASP web page..

Im trying this at the minute, just returns a '0'

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

FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

GROUP BY Applications.StatusCode

 

[SQLBill]

Since you didn't give us the schema information, including datatypes of the columns, let me take a guess....

Your courselist columns are of int/numeric/decimal, etc, but your application.statuscode column is char or varchar.

You want to do:

SELECT (SUM(Courselist.quals)+ SUM(Courselist.quals)) - COUNT(Applications.StatusCode)

-SQLBill

Posting advice: FAQ481-4875

 

[techkate]

meltingpot,

Refer to gmmastros's post above.

One line 1, you include Applications.StatusCode in an arithmetic statement - which means that Applications.StatusCodes should be a number.

Line 3, however, includes Applications.StatusCode='Accepted' as part of the Where clause.

Is Applications.StatusCode stored as a number or text? What some example values?

Kate

[small]"Forever dork"[/small]

 

[meltingpot]

Sorry , yeah
quals=numeric
crew=numeric
StatusCode=varchar

Ive tried your statement, I get a return value of 171 when it should =9

cheers

 

[monksnake]

Take out the SUM statements in SQLBill's query from above

 

[meltingpot]

Im testing it on 1 data feld that I know the values of..

eg
quals=10
+
crew=6
- (add up the 'StatusCode' field that contains the value 'ACCEPTED')9 = 7

 

[SQLBill]

Run these three scripts and post the results:

SELECT SUM(Courselist.quals)
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

SELECT SUM(Courselist.crew
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

SELECT COUNT(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

 

[meltingpot]

monksnake : Ive tried that and get the following error

"You tried to execute a query that does not include the specified expression 'Courselist.quals+Courselist.quals-COUNT(Applications.StatusCode)' as part of an aggregate function."

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

 

[meltingpot]

Cheers SQLBILL, not sure what you mean ?

 

[SQLBill]

Meltingpot...

I gave you a script to run and you said it returned an unexpected value (171 vice 9).

If you run the three scripts I provided, they will return the INDIVIDUAL numbers (before adding or subtracting). Then we can compare that to what you are expecting. Remember, you haven't provided us any sample data and what results you expect...so we have to guess what you really want this script to do. It appears you want to add all the values in each courselist column, then add them together, count the rows in applications.statuscode that have Accepted and subtract that count from the total of the two courselist columns. That is what my script is doing. Now we need to troubleshoot the data.

-SQLBill

Posting advice: FAQ481-4875

 

[meltingpot]

Cheers SQLBILL , that was so simple ... whats the matter with me ???!!!!

thanks

 

[SQLBill]

I have another question for you....on the WHERE.

Courselist.CourseID=ID

Are there really values in CourseID of ID? Or is that intended to be a 'passed in value' (variable/parameter)?

Normally, an xxxxID column is an IDENTITY column and usually a number not a string.

-SQLBill

Posting advice: FAQ481-4875

 

[meltingpot]

Courselist.CourseID=ID is passed via a
RequestQueryString("CourseID")
on a web page. Its the variable that points to a particular course.

That was a simple answer but I wonder how you could do that calculation in one SQL statement ..?

 

[SQLBill]

A variable in SQL Server is identified by @variablename (@ID). Right now your script is comparing Courselist.CourseID to the string ID.

Did the separate scripts give you the numbers you expected? If so, then the original script I posted is the exact same thing combined into one script.

BTW- there is a typo in the middle script I provided. There should be a close parenthesis ')' after the Courselist.crew.

-SQLBill

Posting advice: FAQ481-4875

 

[meltingpot]

Yeah the scripts gave me the right answer in each case,i did notice the parenthesis .

This statement
SELECT COUNT(Applications.StatusCode)
FROM Courselist INNER JOIN Applications ON Courselist.CourseID = Applications.CourseID
WHERE Courselist.CourseID=ID AND Applications.StatusCode='Accepted'

gave me an answer ( how many places are taken on the course) but i also need to calculate how many place are left
i.e


quals+crew - (Applications.StatusCode='Accepted') = places left

Thats the answer I need. Ive tried the following query but returns very odd numbers:

SELECT SUM (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'

?

 

[SQLBill]

That isn't the code I gave you. Try the code that I gave you.

Change your SELECT to this as I originally suggested.

SELECT (SUM(Courselist.quals)+ SUM(Courselist.quals)) - COUNT(Applications.StatusCode)

-SQLBill

Posting advice: FAQ481-4875

 

[meltingpot]

query now looks like this ...

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'

returns this on the page- live example
qulas= 10 (seperate query)
crew = 6 (seperate query)
Taken=5 (correct from seperate query)
Left= 95 (way wrong)

When I 'accepted' (Taken climbs by '1')another place I get the following result

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

thanks for all the help