CODEWITHSUNDEEP
mysqlsql
Vector 2755
Vector 2755

Reputation: 37

Getting unknown column in on clause, error 1054, for this SQL query

Im trying to get this SQL query to work. It wants me to list all product sales for which units sold is greater than the average units sold for that product. In addition to this I need to add a correlated in-line sub-query to the list of average units sold per product.

I created a decent chunk of code here that matches up the logic I was taught in class. The code is commented to describe what I am trying to accomplish with each unique block. I also add a TODO I need to make for the correlated sub query

SELECT * FROM invoice

-- Display sales data from invoice table

join product
on product.P_CODE = line.P_CODE

join line
on line.INV_NUMBER = invoice.INV_NUMBER

-- link tables together to bridge relevant data

having invoice.INV_TOTAL > avg(invoice.INV_TOTAL);

-- display the sales whose total is greater than the average total

/* TODO: Add a correlated in-line sub query to the list of 
average units sold per product here */

Trying to run the query above returns

Error Code: 1054. Unknown column 'line.P_CODE' in 'on clause'

Upvotes: 0

Views: 466

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I don't know what problem you are trying to solve. But if you want invoices whose total is greater than the average, then you don't need all the joins. But you do need a calculation for the average in a subquery.

Something like this:

SELECT i.*
FROM invoice i
WHERE i.inv_total > (SELECT AVG(i2.inv_total)
                     FROM invoice i2);

You would join in the rest of the tables if you needed the columns from them. And you can do that -- no having clause is needed, just the where.

If you want to do this for products, then it would be something like this

SELECT l.*
FROM lines l
WHERE l.units_sold > (SELECT AVG(l2.units_sold)
                      FROM lines l2);

This follows exactly the same pattern.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Switch the positions of your JOINs, i.e. line before product. And your HAVING makes no sense. Maybe you want to use a WHERE and a subquery getting the average.

SELECT *
       FROM invoice i1
            INNER JOIN line l1
                       ON l1.INV_NUMBER = i1.INV_NUMBER
            INNER JOIN product p1
                       ON p1.P_CODE = l1.P_CODE
       WHERE invoice.INV_TOTAL > (SELECT avg(i2.INV_TOTAL)
                                         FROM invoice i2);

And the presence of a column INV_TOTAL in invoice, presumably the sum of the quantity and price from the lines indicates you should normalize your schema here.

Upvotes: 0

Related Questions