Reputation: 570

Beginner SQL database manipulation

I am brand spankin new to SQL, I was trying to complete this little objective, however I keep getting... Empty set (0.00 sec)

Task:

1) List all the items with their extended price (quantity * price)

2) List the total cost of all the items in the store

My input:

mysql> select * from store where price = Qty*Price;

what I believe I have said is... select ALL from "store" where my new updated price is now equal to the Qty*Price..

Which gets me...

Empty set (0.00 sec)

Heres my code for "store".

INSERT INTO `store` (`Id`, `Name`, `Qty`, `Price`) VALUES

    (1, 'apple', 10, 1),

    (2, 'pear', 5, 2),

    (3, 'banana', 10, 1.5),

    (6, 'lemon', 100, 0.1),

    (5, 'orange', 50, 0.2);

Can anyone help as to why this is happening??

Thank you!

Upvotes: 3

Answers (3)

Leslie Tate

Reputation: 570

I ended up using....

SELECT , Qty Price as 'Total Price' From store;

Which combined a few peoples responses and got the answer/format I was looking for.

Upvotes: 0

ctj232

Reputation: 390

The MySQL docs for the WHERE clause say:

The MySQL WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.

so you are trying to filter out all the rows where the value of a column named price equals the values of the columns QTY * PRICE which never occurs in your store table ... hence the empty set of results.

You should update your queries as shown in @rbr94's response

Upvotes: 1

rbr94

Reputation: 2287

1) List all the items with their extended price (quantity * price):

SELECT Qty*Price as extended_price FROM store

2) List the total cost of all the items in the store:

SELECT SUM(Qty*Price) as total_cost FROM store

Upvotes: 2