I have following SQL code
select surname,
(select count(surname))
from employees
group by surname
having count(surname) > 1
order by surname asc;
I need to invoke count method only once.
3 Answers
There are a million ways to do that, but as Gordon pointed out: you shouldn't worry with count being executed multiple times - let the database take care of it. But if you somehow want to avoid writing it twice, try these options:
Subquery
SELECT *
FROM
(SELECT surname, count(surname) AS c
FROM employees GROUP BY surname) j
WHERE c > 1
ORDER BY surname
Or a CTE
WITH j AS (
SELECT surname, count(surname) AS c
FROM employees GROUP BY surname)
SELECT * FROM j WHERE c > 1
ORDER BY surname
1 Comment
Jarek Sularz
Thank you guys, Jim Jones answer's works perfectly. But my code with two duplicates show my poor knowledge about sql, i'm right? ;>
This uses count() only once but uses a subquery
SELECT *
FROM (
SELECT
surname
,count(surname) as surname_count
FROM employees
GROUP BY
surname
)
WHERE
surname_count > 1
ORDER BY
surname ASC
Comments
The other answers seem more complicated than needed, and the subquery construct seems useless in this case and may even slightly affect the performance.
Also
- you don't need to specify the "ASC" sort order, since it's the default
- you don't even need to type the field name twice in
count(surname), and can just typecount(*).
So try this :
SELECT surname, count(*)
FROM employees
GROUP BY surname
HAVING count(*) > 1
ORDER BY surname;
You can also try variations with EXPLAIN in front of your query to see exactly how Postgres procedes.
Comments
count()more than once. Let the database worry about optimization.count(). And you don't need the sub-select. Justselect surname, count(surname) from employees group by surname having count(surname) > 1 order by surname;