how to get row value with group by clause using subquery in PostgreSQL in Laravel 8?-CodePudding

This is my table, first I want to get status_exec of each MAX (date_sta) and after that I want to grouped by status_exec and get the COUNT.

id_out_sta	status_exec	date_sta
1	2	2021-11-07
1	1	2021-11-28
1	5	2021-12-07
2	7	2021-04-02
2	2	2021-05-12
2	6	2021-08-07
3	2	2021-08-05
3	5	2021-08-28
4	2	2021-03-15
4	5	2021-04-25

The result I would expect should be the following:

status_exec	COUNT
5	3
6	1

This is my query but it didn't help:

SELECT id_out_sta, status_exec , max(date_sta) as max_date_sta
FROM public.status_exe 
join public.order_out on status_exe.id_out_sta = order_out.id_out
group by (id_out_sta);

Please any suggestion, query builder or simple query.

CodePudding user response：

A common solution for this is row_number window function to find the maximum of each group. Using this in a CTE and then aggregating the result:

with s as (
    select *,
      Row_Number() over(partition by id_out_sta order by max_date_sta desc) rn
    from t
)
select status_exec, Count(*) "Count"
from s
where rn=1
group by status_exec

Example DB<>Fiddle

CodePudding user response：

Using DISTINCT ON followed by a subquery:

SELECT status_exec, COUNT(*) AS COUNT
FROM
(
    SELECT DISTINCT ON (status_exec) *
    FROM public.status_exe
    ORDER BY status_exec, max_date_sta DESC
) t
GROUP BY status_exec;

CodePudding user response：

Here is another example by using count window function.

SELECT * FROM (
SELECT DISTINCT ON (id_out_sta)
    status_exec,
    count(*) over(partition by status_exec)
  FROM t
  ORDER BY id_out_sta, max_date_sta DESC
) as list
GROUP BY 1,2

Fiddle is here