SQL query not going through due to error in SQL syntax? phpmyadmin

Question

I am typing in this query:

i13 display name, price and sum quantity for all fruit, even ones with quantity null

select inventory.fruitID
concat( "$", quantity * price ) as "value"
from fruit right join inventory
on fruit.fruitID = inventory.fruitID;

I am getting this message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( "$", quantity * price ) as "value" from fruit right join inventory on fruit.fr' at line 3

What am I doing wrong?

are the quotes around the query single '' ie not double ""

user9487972
– user9487972

2018-04-22 22:24:32 +00:00
Commented Apr 22, 2018 at 22:24 — user9487972
– user9487972, Commented Apr 22, 2018 at 22:24
yes they're the double marks "

John Robinson
– John Robinson

2018-04-22 22:25:12 +00:00
Commented Apr 22, 2018 at 22:25 — John Robinson
– John Robinson, Commented Apr 22, 2018 at 22:25
try single ....

user9487972
– user9487972

2018-04-22 22:25:55 +00:00
Commented Apr 22, 2018 at 22:25 — user9487972
– user9487972, Commented Apr 22, 2018 at 22:25
There's a comma missing before the concat().

KIKO Software
– KIKO Software

2018-04-22 22:27:13 +00:00
Commented Apr 22, 2018 at 22:27 — KIKO Software
– KIKO Software, Commented Apr 22, 2018 at 22:27

Gordon Linoff · Accepted Answer · 2018-04-22 22:44:10Z

1

First, if you want all fruit, then use a left join with fruit as the first table. I think the query you are trying for is:

select f.fruitID, f.price, sum(i.quantity) as total_quantity
from fruit f left join
     inventory i
     on f.fruitID = i.fruitID
group by f.fruitID, f.price;

Notes:

Table aliases make the query easier to write and to read.
This assumes that price is in the fruit table.
If you want all fruit, then fruit should be the first table in the left join.
You see to want to sum the inventory, so that requires an aggregation function and group by.

answered Apr 22, 2018 at 22:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query not going through due to error in SQL syntax? phpmyadmin

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related