I have seen many cases with ambiguous problems and tried many stuff, still does not work

Question

i want to know, why in this case, it keeps saying Quantity column is ambiguous, can anybody help?

SELECT SUM(AmountPaid),SUM(Quantity), ice_cream.IceCream
FROM ice_cream 
INNER JOIN ice_cream_ingredient ON (ice_cream.IceCreamID =
ice_cream_ingredient.fkIceCreamID)
INNER JOIN ingredients ON (ice_cream_ingredient.fkIngredientID =
ingredients.IngredientID)
INNER JOIN sales ON (sales.fkIceCreamID =
ice_cream.IceCreamID)
WHERE IceCream='Vanilla Dream'

Does the column Quantity exist on more than one of the tables you are selecting from? If so, then it doesn't know which table to choose to sum the values from. That's what ambiguous means. To fix it, add a table qualifier before the column name. Eg. `sales.Quantity`. — clinomaniac, Jan 08 '18 at 22:52

score 3 · Accepted Answer · answered Jan 08 '18 at 22:41

You should always qualify column names when you have more than one table in a query. In addition, learn to use table aliases.

It is unclear what the correct qualified names are. Here is one guess:

SELECT SUM(s.AmountPaid), SUM(s.Quantity), ic.IceCream
FROM ice_cream ic INNER JOIN
     ice_cream_ingredient ici
     ON ic.IceCreamID = ici.fkIceCreamID INNER JOIN
     ingredients i
     ON ici.fkIngredientID = i.IngredientID INNER JOIN
     sales s
     ON s.fkIceCreamID = ic.IceCreamID
WHERE ic.IceCream = 'Vanilla Dream';

I have seen many cases with ambiguous problems and tried many stuff, still does not work

1 Answers1