CODEWITHSUNDEEP
mysql
Fero
Fero

Reputation: 13315

mysql query needs to be altered

Here is the query which needs to be altered. I have attached the output of the screen. Here i need only the results where maxBuy is less than or equal to totalDealsBought. How should i change in this query for the O/P.

EDIT : ONE more small alter to be done in this query. That is i need to display the result in following format:

1. Where totalDealsBought should be less than maxBuy should display FIRST.

How can this be done?

SELECT d.id, d.dealTitle, d.expiryDate, d.dealMainImage, d.actualPrice, d.discount, d.offerValue, d.maxBuy, sum( sc.quantity ) AS totalDealsBought
    FROM deal AS d
    LEFT JOIN shoppingcart AS sc ON sc.dealID = d.id
    WHERE CURDATE( ) != d.startDate
    AND d.startDate < CURDATE( )
    AND d.status = 'Active'
    AND d.cities = 'chennai'
    AND sc.paymentStatus = 'paid'
    GROUP BY d.id

enter image description here

Thanks in advance.

Upvotes: 2

Views: 86

Answers (2)

Explosion Pills
Explosion Pills

Reputation: 191729

Add the following line HAVING maxBuy <= totalDealsBought to the end of the query.

Upvotes: 6

Shakti Singh
Shakti Singh

Reputation: 86336

Have you tried with 

GROUP BY d.id
HAVING d.maxBuy <= sum(sc.quantity)

Upvotes: 4

Related Questions