CODEWITHSUNDEEP
mysql
John
John

Reputation: 952

Calculate with the total value

I have a table in my database with the following structure:

trans
- id
- user_id
- k_id
- left
- right

I am trying to get the total value in the column left with COALESCE(SUM(left),0) as total1. Also selecting the total value in column right with COALESCE(SUM(right),0) as total2.

For that part the code is working.

After that I want to show the SUM(total1 - total2) AS total

When I run this statement I get an error: Unknown column 'total1' in field list

Does someone know how I can fix this problem?

Here is my full statement:

SELECT COALESCE(SUM(left),0) as total1, COALESCE(SUM(right),0) as total2, SUM(total1 - total2) AS total FROM trans WHERE user_id='2' AND k_id='120'

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In SQL, you cannot re-use column aliases in the SELECT where they are defined. The reason is simple: SQL does not guarantee the order of evaluation of expressions in the SELECT

So, repeat the expressions:

SELECT COALESCE(SUM(left), 0) as total1,
       COALESCE(SUM(right), 0) as total2,
       (COALESCE(SUM(left), 0) - COALESCE(SUM(right), 0)) as total
FROM trans
WHERE user_id = 2 AND k_id = 120;

Note that I removed the single quotes from the constants in the WHERE. Ids are usually numbers. Numbers should be compared to numbers not strings. If the ids really are strings then, of course, the single quotes are appropriate and correct.

Upvotes: 1

Related Questions