Getting a query to work on SQLite as well as on MySQL

Question

I have a subquery with a condition in the main query where clause.

Here is the sample schema

CREATE TABLE `payments` (  
  `id` int(11) NOT NULL,
  `discount_id` int(11),
  `amount` int(11) 
);

CREATE TABLE `discounts` (
  `id` int(11) NOT NULL,
  `rate` int(11) NOT NULL
);

And the query

SELECT 
  discounts.*, 
  (SELECT COUNT(id) 
   FROM payments 
   WHERE payments.discount_id = discounts.id 
   GROUP BY payments.discount_id) AS usage_count
FROM discounts   
WHERE
  rate > 10
  AND usage_count > 1

• Working on SQLite : http://sqlfiddle.com/#!5/9f159/2
• Not working on MySQL : http://sqlfiddle.com/#!9/9f1593/1

MySQL shows error Unknown column 'usage_count' in 'where clause'

I can get it to work on MySQL using a HAVING clause but then it fails on SQLite with TypeError: e.STATEMENT is undefined

• Not working on SQLite : http://sqlfiddle.com/#!5/9f159
• Working on MySQL : http://sqlfiddle.com/#!9/9f1593/2

Is there any way to have a single way of working with both?

Why? I am developing using the Laravel framework, our unit tests run on SQLite and the app server runs MySQL.

Answer 1

Or as a join as your sub-query was only a JOIN and where was based on discounts only.

SELECT 
  discounts.*, count(*) as usage_count
FROM discounts   
JOIN payments
  ON payments.discount_id = discounts.id 
WHERE
  rate > 10
GROUP BY payments.discount_id
HAVING usage_count > 1

Answer 2

SELECT * FROM 
  (SELECT 
  discounts.*, 
  (SELECT COUNT(id) 
   FROM payments 
   WHERE payments.discount_id = discounts.id 
   GROUP BY payments.discount_id) AS usage_count
FROM discounts   
WHERE rate > 10) table1 
WHERE usage_count > 1