how can i fix this syntax error in sql?

Question

SELECT account_name ,sum(total_balance) from transaction 
where account_name IN('cash','a/r','supplies','prepaid','land','gold')
AS total_assets
ORDER BY account_name

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 total_assets order by account_name LIMIT 0, 30' at line 3

I guess you need to need give a name to `sum(total_balance) AS total_assets` — M Khalid Junaid, May 10 '14 at 16:13

score 1 · Answer 1 · answered May 10 '14 at 16:13

1

remove AS total_assets

SELECT account_name ,sum(total_balance) from transaction 
where account_name IN('cash','a/r','supplies','prepaid','land','gold')
ORDER BY account_name

answered May 10 '14 at 16:13

ashishmaurya

1,196
10
18

score 1 · Accepted Answer · answered May 10 '14 at 16:13

You have an alias in the wrong place.

SELECT account_name ,sum(total_balance) from transaction 
where account_name IN('cash','a/r','supplies','prepaid','land','gold')
AS total_assets
ORDER BY account_name

should be

SELECT account_name ,sum(total_balance) AS total_assets
from transaction 
where account_name IN('cash','a/r','supplies','prepaid','land','gold')
ORDER BY account_name

score 0 · Answer 3 · answered May 10 '14 at 16:17

Move the as total_assets into the select clause. This is used to define a column alias:

select account_name, sum(total_balance) as total_assets
from transaction 
where account_name in ('cash', 'a/r', 'supplies', 'prepaid', 'land', 'gold')
order by account_name;

how can i fix this syntax error in sql?

3 Answers3