Question 89913 in PostgreSQL Question posted in
The official documentation can be found here.

sum of two columns assigned to a condition – Postgresql

hi im trying to get the total of two columns stored to a name then get a condition but i having error on the ‘Rebound’ name on line 3

the offreb and defreb has a integer type and some values are stored as 0 (zero)

SELECT team, CONCAT(firstname,' ',lastname) AS name, SUM(offreb + defreb) AS Rebounds
FROM boxscore
WHERE round = 'Finals' AND game = 7 AND Rebounds > 0
ORDER BY team, Rebounds;
Tags:

2

Answers


  1. 0

    You want to filter by column in the WHERE clause which is not yet calculated when the WHERE clause is executed. You can use a sub-query or having.

    It should be something like this:

    SELECT team, CONCAT(firstname,' ',lastname) AS name, SUM(offreb + defreb) AS Rebounds
FROM boxscore
WHERE round = 'Finals' AND game = 7
GROUP BY team, CONCAT(firstname,' ',lastname)
HAVING SUM(offreb + defreb) > 0
ORDER BY team, Rebounds;
    Login or Signup to reply.
  2. 0

    Here using HAVING clause solves your issue.

    If a table has been grouped using GROUP BY, but only certain groups
    are of interest, the HAVING clause can be used, much like a WHERE
    clause, to eliminate groups from the result.
    Official postgres docs

    SELECT
  team,
  CONCAT(firstname,' ',lastname) AS name,
  SUM(offreb + defreb) AS "Rebounds"
FROM 
  boxscore
WHERE
  round = 'Finals' AND game = 7
GROUP BY
  team,
  CONCAT(firstname,' ',lastname)
HAVING
  SUM(offreb + defreb) > 0
ORDER BY
  team, "Rebounds";

    Note that you cannot use column alias in WHERE and GROUP BY clause, but can be used in ORDER BY clause and wrap double quotes to preserve case-sensitivity.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search