I discovered a difference between sqlite3 and psql and I am not sure if it
is intentional but it is definitely inconvenient.
If you use an alias for an aggregate function and then do a GROUP BY with
HAVING, you can't use that alias in the HAVING and you have to type in the
entire function again.
This is not the case with sqlite3 where you can just use the alias. Why
doesn't psql allow this? It seems more efficient.
Take a look at the following example:
-- Schema for sample table named "invoices":
-- id => int
-- customer_id => int
-- total_sale => decimal
Here is the query:
********************
SELECT SUM(total_sale) AS sum_sales FROM invoices GROUP BY customer_id
HAVING sum_sales > 20 ORDER BY sum_sales DESC;
********************
This code above works in sqlite3 but not psql. For psql I have to do the
following:
********************
SELECT SUM(total_sale) AS sum_sales FROM invoices GROUP BY customer_id
HAVING SUM(total_sale) > 20 ORDER BY sum_sales DESC;
********************
Regards,
Prateek.