0

I have a query that looks like the following:

select uid,leadcount,salescount,leadcount/salescount as diff from 
(
SELECT x.uid, COUNT(*) leadcount, COUNT(DISTINCT x.fid)
FROM total_leads AS x
WHERE x.uid BETWEEN 1 AND 5
GROUP BY x.uid
) t1 left join 
(
SELECT ud.UserId, COUNT(*) salescount, COUNT(DISTINCT ud.SalesID)
FROM total_sales AS ud
WHERE ud.UserId BETWEEN 1 AND 5
GROUP BY ud.UserID
) t2 on t1.uid=t2.UserId

It results in:

enter image description here

I'm trying to display only results where diff is greater than 2.5 (so in this case only uid 5 should be visible on output).

I tried using WHERE diff >= 2.5 but I got "You have an error in your SQL syntax".

Working example of MySQL

ctfd
  • 338
  • 3
  • 14

2 Answers2

2

Just repeat the original expression in the WHERE clause rather than using an alias:

SELECT uid leadcount, salescount, leadcount/salescount AS diff
FROM
(
    ...
) t
WHERE leadcount/salescount >= 2.5;

The problem with referring to diff in the WHERE clause is that it is not yet available. Also, see Gordon's answer for another option, assuming you don't plan to ever aggregate. But, using HAVING is only an option on MySQL or MariaDB.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    @ctfd It is because often the `WHERE` clause will execute _before_ the alias has actually been assigned. `diff` simply does not exist when the `WHERE` clause happens. – Tim Biegeleisen Feb 18 '19 at 02:43
1

In MySQL and MariaDB, you can just add a HAVING clause to the end of the query:

HAVING diff >= 2.5

This acts like a WHERE but can use the alias.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786