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.

• mustaccio: Now you know why it is a Bad IdeaTM to use different databases for development, test, and production. You might be able to find a solution in this case, but not necessarily in other cases, and something that might work in both DBMSes won't be optimal in either.
• Vincent Mimoun-Prat: Rare occurrences of complex queries in that project. Gain of using in-memory SQLite DB for tests is quite huge in terms of developer comfort. That's ok to sacrifice some time sometimes in order to save a lot every day. I could always use a specific test running under a dev instance of MySQL (which is done for some other tests). Just curious if I am missing some SQL concepts, that is not my strenght
• mustaccio: You're welcome to shoot yourself in the foot in whichever way you deem most appropriate.
• Vérace: I don't think it's a big undertaking to install MySQL on any machine! Ghost a machine image for your devs?
• danblack: or a docker container containing the database and loaded test data.

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

• Vincent Mimoun-Prat: Thanks. Other answer accepted as it will be easier to setup using the Eloquent ORM we use (in Laravel)