CODEWITHSUNDEEP
mysql
Zincktest
Zincktest

Reputation: 749

Select and multiply

I am new to MySQL and trying to learn.

I have two tables with (I only show the columns, which we should use)

Orderdetails (ordernumber, quantityOrdered, priceEach)

and

Orders (ordernumber)

I want to mutiply quantityOrdered and priceEach for one ordernumber. So then I get a row that shows the columns orderNumber and Total.

This is what I tried, but i could not figure it out. Should I maybe join?

SELECT orderNumber, SUM((quantityOrdered * priceEach)) AS Total 
      GROUP BY orderNumber       <<=Here it says syntax error?
      FROM orderdetails

How about join my orders and orderdetails table?

Upvotes: 1

Views: 193

Answers (2)

Jason
Jason

Reputation: 15335

Based on your comment to @Michael Wright' s answer I think you want this:

SELECT `ordernumber`, SUM((`quantityOrdered` * `priceEach`)) AS `total` 
    FROM `orderdetails`
    GROUP BY `ordernumber`;

That should give you the order number and the sum of all quantityOrdered * priceEach.

If you want a specific ordernumber you'l want to try:

SELECT `ordernumber`, SUM((`quantityOrdered` * `priceEach`)) AS `total` 
    FROM `orderdetails`
    WHERE `ordernumber` = 'X'
    GROUP BY `ordernumber`;

Upvotes: 0

user835223
user835223

Reputation:

You should be able to use: 

SELECT `ordernumber`, (`quantityOrdered` * `priceEach`) AS `total` FROM `orderdetails`;

That will return a column called total that has the product of the two columns, as well as the ordernumber returned.

Upvotes: 1

Related Questions