CODEWITHSUNDEEP
mysqlsqldatabasesubtraction
Peter Brennan
Peter Brennan

Reputation: 3

subracting two columns after firstly adding two columns together

I'm learning SQL right and I'm having a little trouble with a query I want to implement in my webpage. I hosting my webpage on my own server and I use MySQL Workbench ver. 5.2.47CE (the latest). Now to create my webpage I am using Adobe Dreamweaver CS6.

What I want to do is add 2 columns together and subtract that total from another column. A - ( B + C ) = 'result'

This is based on a game where A = TOTAL DEATHS AND B+C = TOTAL KILLS. If I subtract these two I will end up with TOTAL SUICIDES.

This is what I have come up with atm...... 

SELECT
  (SELECT SUM(is_dead)
   FROM survivor
   WHERE (is_dead=1)
  )-
  ((SELECT SUM(bandit_kills)
     FROM survivor
    ) +
    (SELECT SUM(survivor_kills)
     FROM survivor)
   ) AS SUICIDES

Now when I run this query in MySQL Workbench it works! I receive the correct answer!

So I copied the code right and create new recordset with the SQL query in Dreamweaver CS6. When I click the TEST button in the create new recordset, it returns with the right value and everything seems to pass the TEST. I click OK to save the new recordset.

This is where the error happens. When I go to select the new RECORDSET to insert into a table it shoots this error.

MySQL Error#: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AS SUICIDES

WHERE 0 = 1' at line 1

Upvotes: 0

Views: 599

Answers (3)

Peter Brennan
Peter Brennan

Reputation: 3

THANKS contradictioned that's what I was looking for....

the query 

SELECT SUM(is_dead)
FROM survivor

gives me the sum of all players within the table is_dead with a value of 1 ( 1 being dead )

thank you so much...

Upvotes: 0

echo_Me
echo_Me

Reputation: 37253

why all those subqueries? you should it easily like that.

      SELECT SUM(is_dead)-( SUM(bandit_kills)+ SUM(survivor_kills)) AS SUICIDES 
      FROM survivor   WHERE is_dead=1

Upvotes: 0

contradictioned
contradictioned

Reputation: 1253

Looks like Dreamwaver is somehow rewriting your query, such that there is this WHERE 0 = 1 appended. If this is appended direct after the SELECT-clause, it is obviously an SQL syntax error.

Assuming your is_dead column has only values 0 and 1, you can do the maths a little easier and have a from clause which should lead Dreamwaver to a correct syntax:

SELECT SUM(is_dead) - ( SUM(bandit_kills) + SUM(survivor_kills) )
FROM survivor

Still this is no explanation, why WHERE 0 = 1 is appended. See http://sqlfiddle.com/#!2/336f4/2 for playing around

Upvotes: 2

Related Questions