MySQL using `IN` while having multiple values

Question

I am trying to count of coupons sold by each store from the list of stores within 20 miles range. I know the following syntax will work if there is only 1 store.

SELECT sum(couponscount) as count where restaurant IN (SELECT storename where bhal bhal bhal and output is one value)

What is I the IN (SELECTstorenamewhere bhal bhal bhal and output is multiple values) will return multiple values?

Like in my case the complete SQL is like and its not working

SELECT sum(couponscount) as count FROM `coupons` having `restaurant` IN (SELECT `storename`, ((ACOS(SIN(-27.561264299999998 * PI()/180) * SIN(latitude * PI()/180) + COS(-27.561264299999998 * PI()/180) * COS(latitude * PI()/180) * COS((153.07304890000003 – longitude) * PI()/180)) *180 / PI( )) *60 * 1.1515) AS `distance` FROM `stores` WHERE `status`=’active’ HAVING `distance` <=20)

Is there anyway to make it working?

You need to move the distance condition from the SELECT to WHERE. — Mihai
– Mihai, Commented Sep 9, 2014 at 14:55
As a rule of thumb: When you see HAVING without GROUP BY there is usually something wrong with the query. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 9, 2014 at 15:05
@Mihai: Yes, also in MySQL. It usually shows that the author of the query doesn't exactly know what he/she is doing :-) — Thorsten Kettner
– Thorsten Kettner, Commented Sep 9, 2014 at 15:09

Mihai · Accepted Answer · 2014-09-09 15:23:24Z

1

SELECT sum(couponscount) AS COUNT,restaurant
FROM `coupons`
WHERE `restaurant` IN
    (SELECT `storename`
     FROM `stores`
     WHERE `status`='active'           
       AND
      ((ACOS(SIN(-27.561264299999998 * PI()/180) * SIN(latitude * PI()/180) + COS(-27.561264299999998 * PI()/180) * COS(latitude * PI()/180) * COS((153.07304890000003 – longitude) * PI()/180)) *180 / PI()) *60 * 1.1515) <=20)
GROUP BY restaurant

Also use proper quotes for active.

edited Sep 9, 2014 at 15:23

answered Sep 9, 2014 at 14:58

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karthik Malla Over a year ago

Still didn't work even after your edited the answer. Error Unknown column 'latitude' in 'having clause' but that latitude is used column in the table but there is something wrong in your syntax.

Gordon Linoff · Accepted Answer · 2014-09-09 14:57:55Z

1

Presumably, you want to get the count of coupons from stores within a distance of 20. Moving the having condition to a where clause should do what you want:

SELECT sum(couponscount) as count
FROM `coupons`
WHERE `restaurant` IN (SELECT `storename`
                       FROM `stores`
                       WHERE `status` = 'active' AND
                              ((ACOS(SIN(-27.561264299999998 * PI()/180) * SIN(latitude * PI()/180) + COS(-27.561264299999998 * PI()/180) * COS(latitude * PI()/180) * COS((153.07304890000003 – longitude) * PI()/180)) *180 / PI( )) *60 * 1.1515) <= 20
                      );

You had a major syntax problem because your subquery returned two columns. When you use a subquery with in, you can only return one column, in this case, storename. I moved the code for the distance calculation to the where clause. No having clause is needed either in the subquery or the outer query.

answered Sep 9, 2014 at 14:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Karthik Malla Over a year ago

Lol, that returned complete count of all stores but I need count per each store so that I can use a while loop in php

Collectives™ on Stack Overflow

MySQL using `IN` while having multiple values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related