Query don't return the right result

Question

I have a POST table, a ACTION table and ACTION_TYPE table, I explain the ACTION table contains all the actions that were made by users, and the table ACTION_TYPE contains the actions details for example the ACTION whose ID = 4 has ACTION_TYPE_ID = 1 for POST_ID 6, which mean an action was made for post number 50, we can have many actions for one post_id

the POST table

 id         title       content     user_id
----------  ----------  ----------  ----------  
 1          title1      Text...     1
 2          title2      Text...     1
 3          title3      Text...     1
 4          title4      Text...     5
 5          title5      Text...     2
 6          title6      Text...     1

the ACTION_TYPE table

 id         name      
----------  ----------  
 1          updated
 2          deleted
 3          restored
 4          hided

the ACTION table

 id         post_id         action_type_id    date
----------  ----------      --------------    -----
 1          1               1                 2017-01-01
 2          1               1                 2017-02-15
 3          1               3                 2018-06-10
 4          6               1                 2019-08-01
 5          5               2                 2019-12-09
 6          2               3                 2020-04-27
 7          2               1                 2020-07-29
 8          3               2                 2021-03-13

So i would like to know the last action are made for each post sometimes i would like to get teh last action made by specific action_type and user for each post.

here is my query

select actions, count(*) as cnt
   from(
        select ac.post_id as action_post_id, max(ac.date) as max_date, 
        case
        when ac.action_type_id is not null then act.name
        end as actions,
        case
        when p.user_id is not null then u.name
        end as user_name
        from action ac
        left join post p on ac.post_id = p.id  
        left join user u on p.user_id = u.id
        left join action_type act on ac.action_type_id = act.id
        where p.user_id = 1
        and act.name in ('restored','deleted','updated')
        group by ac.post_id, case when ac.action_type_id is not null then act.name end , case when p.user_id is not null then u.full_name end 
            )
            group by actions
;

so here is one probleme i want to groupe by post_id but it ask me to add cases too so i get the following error: /ORA-00979. 00000 - "not a group by expression/ when i delete the cases from group by but when i use this query i get false result

here the result i get

 actions    user_name   cnt
----------  ----------  -----------   
updated      ERIC       2
deleted      ERIC       2
restored     ERIC       2

so here the result expected to be

 actions    user_name   cnt
----------  ----------  -----------   
updated      ERIC       2
deleted      ERIC       1
restored     ERIC       1

the sum must equal 4 but i'm geting 6 it's like if it brings more than one action per post

Important ! when i use simple query to check manualy actions the sum equal 4

Best regards

Answer 1

You can use:

SELECT MAX(t.name) AS action_name,
       MAX(u.name) AS user_name,
       COUNT(*) AS number_posts
FROM   users u
       INNER JOIN post p
       ON (u.id = p.user_id)
       INNER JOIN (
         SELECT post_id,
                MAX(action_type_id) KEEP (DENSE_RANK LAST ORDER BY "DATE", id)
                  AS action_type_id
         FROM   action
         GROUP BY post_id
       ) a
       ON (p.id = a.post_id)
       INNER JOIN action_type t
       ON (t.id = a.action_type_id)
GROUP BY
      u.id,
      t.id

Which, for the sample data:

CREATE TABLE users (id, name) AS
SELECT 1, 'Eric' FROM DUAL;

CREATE TABLE POST ( id, title, content, user_id) AS
SELECT 1, 'title1', 'Text...', 1 FROM DUAL UNION ALL
SELECT 2, 'title2', 'Text...', 1 FROM DUAL UNION ALL
SELECT 3, 'title3', 'Text...', 1 FROM DUAL UNION ALL
SELECT 4, 'title4', 'Text...', 5 FROM DUAL UNION ALL
SELECT 5, 'title5', 'Text...', 2 FROM DUAL UNION ALL
SELECT 6, 'title6', 'Text...', 1 FROM DUAL;

CREATE TABLE ACTION_TYPE ( id, name ) AS
SELECT 1, 'updated'  FROM DUAL UNION ALL
SELECT 2, 'deleted'  FROM DUAL UNION ALL
SELECT 3, 'restored' FROM DUAL UNION ALL
SELECT 4, 'hided'    FROM DUAL;

CREATE TABLE ACTION ( id, post_id, action_type_id, "DATE") AS
SELECT 1, 1, 1, DATE '2017-01-01' FROM DUAL UNION ALL
SELECT 2, 1, 1, DATE '2017-02-15' FROM DUAL UNION ALL
SELECT 3, 1, 3, DATE '2018-06-10' FROM DUAL UNION ALL
SELECT 4, 6, 1, DATE '2019-08-01' FROM DUAL UNION ALL
SELECT 5, 5, 2, DATE '2019-12-09' FROM DUAL UNION ALL
SELECT 6, 2, 3, DATE '2020-04-27' FROM DUAL UNION ALL
SELECT 7, 2, 1, DATE '2020-07-29' FROM DUAL UNION ALL
SELECT 8, 3, 2, DATE '2021-03-13' FROM DUAL;

Outputs:

ACTION_NAME	USER_NAME	NUMBER_POSTS
restored	Eric	1
updated	Eric	2
deleted	Eric	1

fiddle