Postgresql – How to count rows based on a column value

postgresqlwindow functions

I am using Posgresql and I want to write a query to perform the following.
Suppose the table is like this:

id  section views
1     1      10
2     2       8
3     1      20
4     1      21
5     2      0

I want to count each row that has the same "section" value (ordered by "views") like this:

 id  section views contador 
 4   1        21     1       //section 1 contador 1
 3   1        20     2       //section 1 contador 2 ...
 1   1        10     3
 2   2         8     1   //section 2 contador 1
 5   2         0     2   //section 2 contador 2

Best Answer

You need to use a Window Function like row_number()

SELECT
  id,
  section,
  views,
  row_number() OVER (PARTITION BY section ORDER BY views DESC)
FROM table;

Related Solutions

Postgresql – Understanding rows count computation

Since it was not in most_common_vals, PostgreSQL then looks in the histogram. If there is no histogram, it would conclude the most common values are the only values present in the table, and therefore the estimate for 123123 is zero. But it doesn't allow zero estimates in most places, to prevent div by zero error, and instead clamps it at 1.

0.5% is for cases where there is no information, like for a generic query plan or a join where the value won't be known at planning time.

But having a list of MCV and the one you want not being present in that list and there being no histogram to fallback on does constitute information.

Postgresql – Schema name, table name and row count

This should do the trick:

select nspname, relname, relkind, reltuples from pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where relkind in ('r', 'v');

Note that the reltuples is an estimate of the rows not the actual count at the moment but generally it's quite accurate (depends on how up to date your statistics are).

Best Answer

Related Solutions

Postgresql – Understanding rows count computation

Postgresql – Schema name, table name and row count

Related Question