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

Postgresql – need a function that divides the total number of rows by the number of rows with specific text

I can’t write a condition that will allow me to count the total number of lines, divide them by the number of lines that contain the word Tetris and display the result of the division

Trying

Select count(*) 
From table_name
Where main/(select main from table_name where main="Tetris")

enter image description here

Tags:

3

Answers


  1. 0

    The data that you get from the database is only what you put in the SELECT clause. The WHERE clause is exclusively here to filter according to a predicate.

    Your query would work by just moving the troublesome part inside the SELECT clause. Note that to have a ratio, you need to convert COUNT(*) to a floating-point number with a cast operation.
    Lastly, string values in sql are single-quoted. Double-quotes are for identifiers.

    SELECT COUNT(*)::DECIMAL / (SELECT COUNT(*) FROM table_name WHERE main='Tetris')
FROM table_name

    A slightly better version of the above makes use of FILTER for aggregation functions:

    SELECT COUNT(*)::DECIMAL / COUNT(*) FILTER (WHERE main='Tetris')
FROM table_name

    Or here is a version with CASE, that makes use of the fact that NULL values do not participate to the counts.

    SELECT COUNT(*)::DECIMAL / COUNT(CASE WHEN main='Tetris' THEN 1 ELSE NULL END)
FROM table_name
    Login or Signup to reply.
  2. 0

    You could sum one for every row divided by the sum of every Tetris row:

    SELECT  SUM(1.0) / SUM(CASE WHEN main='Tetris' THEN 1 END)
FROM    table_name
    Login or Signup to reply.
  3. 0

    What you are trying to compute is the reciprocal of the occurrence rate of ‘tetris’. Other than the various possible solutions offered by Atmo, here is an alternative using the aggregate function AVG.

    SELECT 1.0 / AVG(CASE WHEN main = 'tetris' THEN 1.0 ELSE 0.0 END)
FROM table_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search