Issue

This Content is from Stack Overflow. Question asked by zachs snachs

So I have this problem:

Create a function skillsInRange(n1 int, n2 int) returns the count ofWesterosis that have at least n1 skills and at most n2 skills. Test yourqueries with inputs:

With the relevant table:

”’
INSERT INTO WesterosiSkill VALUES (1001,’Archery’), (1001,’Politics’), (1002,’Archery’), (1002,’Politics’), (1004,’Politics’), (1004,’Archery’), (1005,’Politics’), (1005,’Archery’), (1005,’Swordsmanship’), (1006,’Archery’), (1006,’HorseRiding’), … ………………………..

”’
(wid, skill) — Columns

So, it seems obvious that we only need to count the duplicate values in wid (first column). And then our second condition is only keeping the ones that have between n1 and n2 occurrences.

So I created this function:

CREATE FUNCTION skillsInRange (n1 int, n2 int)
RETURNS INTEGER AS
$$
SELECT COUNT(wid) AS wcount
FROM westerosiSkill
GROUP BY wid
HAVING wcount BETWEEN n1 AND n2
$$ LANGUAGE SQL;

And a few variants of it. None of which have worked. In this version, it tells me that ‘wcount’ doesn’t exist even when I change ‘HAVING’ -> ‘WHERE’. The SELECT COUNT, FROM, GROUP BY returns the incorrect number of occurrences for each wid. And any of the ‘HAVING’ clauses I’ve tried returns the incorrect number of occurrences between these two values, or in this instance, is syntactically incorrect due to the aggregate functions.

Solution

You need two levels of aggregation:

CREATE FUNCTION skillsinrange (n1 int, n2 int)
  RETURNS int
  LANGUAGE sql AS
$func$
SELECT count(*)::int
FROM  (
   SELECT count(*)::int AS wcount
   FROM   westerosiSkill
   GROUP  BY wid
   ) sub
WHERE wcount BETWEEN n1 AND n2;
$func$;

This is assuming that (wid, skill) is the PK, so both columns are defined NOT NULL, and the combination UNIQUE. Else you need to do more.

The function wrapper seems arbitrary. You might just use the plain query.

The cast to integer is just because you show a function signature with type integer for input and output. You might use bigint instead and ditch the casts.

For the error messages you saw, consider the manual:

An output column’s name can be used to refer to the column’s value in
ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING
clauses; there you must write out the expression instead.

See:

PostgreSQL Where count condition

This Question was asked in StackOverflow by zachs snachs and Answered by Erwin Brandstetter It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.