CODEWITHSUNDEEP
mysqlsql
Karthik Malla
Karthik Malla

Reputation: 5800

MySQL using `IN` while having multiple values

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?

Upvotes: 0

Views: 170

Answers (2)

Mihai
Mihai

Reputation: 26784

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

Related Questions