CODEWITHSUNDEEP
mysqlsql
SaberTooth
SaberTooth

Reputation: 75

Mysql Query Calculation Issues?

I am trying the following query to find out the Discounted values within the query itself.

I had tried every way to figure out what is happening here, but I think i am going somewhere else:

Here is the query 

SELECT
sales.quantity,
sales.sell_date,
items.itemname,
orders.orderunique,
orders.issold,
orders.revisionNumber,
orders.discountoffered,
categories.catName,
items.unitprice,
items.unitcost,
items.itemcode,
customer.customer,
orders.trackingref,
tbl_suppliers.suppliername,
items.unitprice-items.unitcost AS Profit,
((items.unitprice * orders.discountoffered/100) as discountedprice),
(items.unitprice-`discountedprice` AS discountcalculated),
(items.unitprice-`discountcalculated` AS DiscountedProfit) 
FROM
sales
INNER JOIN orders ON sales.orderID = orders.orderID
INNER JOIN items ON sales.itemID = items.itemID
INNER JOIN categories ON categories.catID = items.categoryID
INNER JOIN customer ON sales.customerID = customer.customerID
INNER JOIN tbl_suppliers ON tbl_suppliers.ID = items.supplierID
where sales.itemID = 3 and issold = 'yes'

This is the Error I am getting: 

[Err] 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 discountedprice),
(items.unitprice-`discountedprice` AS discountcalculated),' at line 17

Upvotes: 0

Views: 40

Answers (2)

VMai
VMai

Reputation: 10336

You can't use column alias names in the column list of your query:

Problems with Column Aliases states

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

So you've got to rewrite this part of your query:

SELECT
    [....],
    ((items.unitprice * orders.discountoffered/100) as discountedprice),
    (items.unitprice-`discountedprice` AS discountcalculated),
    (items.unitprice-`discountcalculated` AS DiscountedProfit) 
FROM
    [...]

and write the expressions out:

SELECT
    [....],
    (items.unitprice * orders.discountoffered/100) as discountedprice,
    items.unitprice - (items.unitprice * orders.discountoffered/100) AS discountcalculated,
    items.unitprice - (items.unitprice - (items.unitprice * orders.discountoffered/100)) AS DiscountedProfit) 
FROM
    [...]

But you've got a logical error in your DiscountedProfit somehow because your discountedprice equals your DiscountedProfit, see it:

items.unitprice - (items.unitprice - (items.unitprice * orders.discountoffered/100))

evaluates to

(items.unitprice * orders.discountoffered/100)

and that's your definition of the discountedprice column.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Try this 

SELECT
sales.quantity,
sales.sell_date,
items.itemname,
orders.orderunique,
orders.issold,
orders.revisionNumber,
orders.discountoffered,
categories.catName,
items.unitprice,
items.unitcost,
items.itemcode,
customer.customer,
orders.trackingref,
tbl_suppliers.suppliername,
items.unitprice-items.unitcost AS Profit,
((items.unitprice * orders.discountoffered)/100) as discountedprice,
(items.unitprice-`discountedprice` ) AS discountcalculated,
(items.unitprice-`discountcalculated` ) AS DiscountedProfit 
FROM ...

You are giving an alias to calculated column inside parenthesis like (.. AS discountcalculated)) which is causing error move it outside like (col1 -col2 ) AS col_name

Also you can't use the computed alias at same level either you have to repeat the whole calculation expression or use a subselect 

SELECT
... ,
items.unitprice-items.unitcost AS Profit,
((items.unitprice * orders.discountoffered)/100) as discountedprice,
(items.unitprice - ((items.unitprice * orders.discountoffered)/100) ) AS discountcalculated,
(items.unitprice -(items.unitprice- ((items.unitprice * orders.discountoffered)/100) ) ) AS DiscountedProfit 
FROM ...

Upvotes: 0

Related Questions