CODEWITHSUNDEEP
mysqlsqlcodeigniter
Kris
Kris

Reputation: 109

Simple Sum and Subtract Syntax Error

Table1: id | car1 | car2 | car3 | car4

**Table2:**
id | windows | doors | engines

I have this sql code:

sum(car1) + sum(car2) + sum(car3) + sum(car4) as 'tot_cars',
sum('doors') as 'tot_doors'
'tot_doors' - 'tot_cars' as 'door_rest'

I've tried this also and not working:

sum(car1) + sum(car2) + sum(car3) + sum(car4) as 'tot_cars',
sum('doors') as 'tot_doors'
('tot_doors' - 'tot_cars' as 'door_rest')

This:

sum(car1) + sum(car2) + sum(car3) + sum(car4) as 'tot_cars',
sum('doors') as 'tot_doors'
('tot_doors - tot_cars as door_rest')

And this:

sum(car1) + sum(car2) + sum(car3) + sum(car4) as 'tot_cars',
sum('doors') as 'tot_doors'
('tot_doors - tot_cars') as 'door_rest'

All not working.

Still I'm having errors in SQLSyntax, it said cannot find 'door_rest' field in the database.

There are no problem with joining the two tables, because when I was querying the 'tot_cars' it showed the data on my page. But I'm having trouble with the 'door_rest' field.

Thank you for the help.

Upvotes: 2

Views: 551

Answers (3)

fthiella
fthiella

Reputation: 49069

Aliases are not available on the SELECT clause, so you have to repeat the sum:

select
  sum(car1) + sum(car2) + sum(car3) + sum(car4) as tot_cars,
  sum(doors) as tot_doors,
  sum(doors) - sum(car1) - sum(car2) - sum(car3) - sum(car4) as door_rest

also you can just use one sum function for each calculated field (unless you have null values, but you have to fix your original query anyway if there are some null values):

select
  sum(car1 + car2 + car3 + car4) as tot_cars,
  sum(doors) as tot_doors,
  sum(doors - car1 - car2 - car3 - car4) as door_rest

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Do not use single quotes for column aliases. The value of:

sum('doors')

is 0, because it is summing the string 'doors'. And, MySQL converts strings to integers based on leading digits -- which results in 0 in this case. Only escape names that need to be escaped, to avoid extra typing and make your queries more clear. Use the proper escape character (a backtick). And then choose names that don't need to be escaped.

Your problem is that you cannot use an alias in the same select, so:

sum(car1) + sum(car2) + sum(car3) + sum(car4) as tot_cars,
sum(doors) as tot_doors,
(sum(car1) + sum(car2) + sum(car3) + sum(car4) - sum(doors)) as door_rest

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93734

You cannot use alias name in same select query. Use the calculated field in outer query and make the original query as sub-select

select (tot_doors - tot_cars) as 'door_rest'
(
sum(car1) + sum(car2) + sum(car3) + sum(car4) as 'tot_cars',
sum(doors) as 'tot_doors'
from yourtable
) A

This is how query is logically proccessed

FROM clause
WHERE clause
SELECT clause
GROUP BY clause
HAVING clause
ORDER BY clause

Upvotes: 0

Related Questions