CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbench
don
don

Reputation: 65

How can I count items from purchase price?

I am stuck trying to write a mysql query. The requirement is to count the number of white and red items, which I can purchase for a total $20 price. Here is my attempted query:

SELECT colour, purchasePrice
FROM `product`

It returns this result link. But, I want to count how many white and red items are in the $20 price. For example, imagine I have a budget of $20, and each item costs $2.00. Therefore, I know I can only buy 10 of them. I hope this is easy to understand.

Upvotes: 1

Views: 287

Answers (1)

JohnHC
JohnHC

Reputation: 11205

For MySQL 8.0 onwards, there are Window Functions to create a cumulative price for the colours permitted:

select count(*) -- counts the number of items
from
(
select sum(p1.purchasePrice) over (order by p1.purchasePrice asc) as c_price -- Our cumulative price
from product p1
where colour in ('white', 'red') -- limit the colours
) x2
where  x2.c_price <= 20 -- where the cumulative is less than the budget

EDIT: It seems you are looking for how many of each item you can buy, rather than how many from the list:

select colour, purchasePrice,
       floor(20/purchasePrice) as qty_for_20 -- floor rounds the number down
from products
where colour in ('white', 'red')

Upvotes: 1

Related Questions