CODEWITHSUNDEEP
sqlpostgresql
cskr
cskr

Reputation: 87

PostgreSQL Current count of specific value

I need to achieve a view such as:

+------------+----------+--------------+----------------+------------------+
|  Parent id | Expected | Parent Value | Distinct Value | Distinct Value 2 |
+------------+----------+--------------+----------------+------------------+
|          1 |  001.001 |            3 | 6/1/2017       |         5,000.00 |
|          1 |  001.002 |            3 | 9/1/2018       |         3,500.00 |
|          1 |  001.003 |            3 | 1/7/2018       |         9,000.00 |
|          2 |  002.001 |            7 | 9/1/2017       |         2,500.00 |
|          3 |  003.001 |            5 | 3/6/2017       |         1,200.00 |
|          3 |  003.002 |            5 | 16/8/2017      |         8,700.00 |
+------------+----------+--------------+----------------+------------------+

where I get distinct child objects that have same parents, but I cannot make the "Expected" column work. Those zeros don't really matter, I just need to get subindex like "1.1", "1.2" to work. I tried rank() function but it seems it doesnt really help.

Any help appreciated, thanks in advance.

My initial try looks like this:

SELECT DISTINCT
  parent.parent_id, 
  rank() OVER ( order by parent_id ) as expected,
  parent.parent_value,
  ct.distinct_value,
  ct.distinct_value_2
FROM parent
LEFT JOIN (crosstab (...) ) 
  AS ct( ... ) 
ON ...

Upvotes: 1

Views: 37

Answers (1)

klin
klin

Reputation: 121804

Use partition by parent_id in the window function and order by another_col to define the order in groups by parent_id.

with parent(parent_id, another_col) as (
    values (1, 30), (1, 20), (1, 10), (2, 40), (3, 60), (3, 50)
)

select 
    parent_id, 
    another_col,
    format('%s.%s', parent_id, row_number() over w) as expected
from parent
window w as (partition by parent_id order by another_col);

 parent_id | another_col | expected 
-----------+-------------+----------
         1 |          10 | 1.1
         1 |          20 | 1.2
         1 |          30 | 1.3
         2 |          40 | 2.1
         3 |          50 | 3.1
         3 |          60 | 3.2
(6 rows)

Upvotes: 1

Related Questions