CODEWITHSUNDEEP
phpmysqldata-manipulation
venichhe
venichhe

Reputation: 37

MySQL - Selecting the sum amount based on specific condition

I've searched for a few hours now, but couldn't find relative solution to a specific algorithm I am working on. To simplify the obstacle, I would like to present the information in just one table.

_____________________________________
|  User  |  Item  |  price  |  qty  |
-------------------------------------
|  Annie | Dress  |   80    |  1    |
|  Bob   | Jeans  |   65    |  3    |
| Cathy  | Shoes  |   60    |  4    |
| David  | Shirts |   40    |  6    |
| Annie  | Shoes  |   60    |  2    |
|  Bob   | Shirts |   55    |  2    |
| Cathy  | Jeans  |   65    |  1    |
| David  | Ties   |   20    |  5    |
-------------------------------------

Problem # 1: Show users whose total price for shopping at the store is 300 or more and quantity of their purchase is less than or equal to 3. These shoppers will be mailed a coupon for $40.

Problem # 2: Show users whose total qty is greater than or equal to 7 and the total for price is 275 or more. These shoppers will be mailed a coupon for $20.

The rows within the table are not transaction specific. The table can represent separate transactions within a month. We're just trying to find certain returning customers who we would like to reward for shopping with us.

I'm not sure if this can be done only via MySQL, or if I need to have separate queries and store rows into arrays and compare them one by one.

What I have tried so far are the followings:

SELECT * FROM table where SUM(price) as Total >= 300 AND SUM(qty) <=3;

I've also tried the following after the research:

SELECT SUM(price) as Total FROM table WHERE SUM(qty) <=3;

I keep getting syntax errors in MySQL shell. You don't have to solve the problems for me, but if you can guide me through the logic on how to solve the problems, I'd appreciate it very much.

Lastly I'd like to ask once, can I solve this with only MySQL or do I need to store the rows into PHP arrays and compare each indexes?

Upvotes: 0

Views: 190

Answers (2)

Barmar
Barmar

Reputation: 780787

You can't use an aggregate function in the WHERE clause, you have to use HAVING. WHERE operates on individual rows during the selection, HAVING operates on the final results after aggregating.

SELECT *, SUM(price*qty) as Total
FROM table
GROUP BY user
HAVING Total >= 300 AND SUM(qty) <= 3

Upvotes: 3

Rhythem Aggarwal
Rhythem Aggarwal

Reputation: 356

SUM is an aggregate function, meaning it applies to a group of clubbed rows. S say i am grouping the table data based on NAME then sum function would sum all the price of one NAME. Having said this, if you think logically it would not make any sense to put the sum(price) in a WHERE clause because where clause would not know which SUM(PRICE) for which NAME to operate on(where clause operates only after a temporary view has been generated). So we have the HAVING clause in SQL. This is used to compare the results of aggregrate function at each step of aggregation.

Consider it like this: In where clause, when the ANNIE row from your DB is returned, it does not know what SUM(PRICE) means. While in HAVING clause the SUM(PRICE)>300 condition is executed only when SQL has finished grouping all the ANNIE data into one group and calculated the SUM(PRICE) for her.

For question 1:

SELECT USER, SUM(PRICE) 
FROM table
GROUP BY user
HAVING SUM(PRICE) >= 300 AND SUM(QTY) <= 3

For Question 2:

SELECT USER, SUM(PRICE) 
FROM table
GROUP BY user
HAVING SUM(PRICE) >= 275AND SUM(QTY) >=7

Upvotes: 0

Related Questions