I am struggling with selecting count of maximal value of the status for each author. I have a log database that is saving every change to a ticket - from sending, accepting to solving.

Data is being uploaded by stored procedures.

When someone sends a ticket it receives status 0, when someone accepts it gets 1, when it's getting done it's getting 2 and when it's declined it gets 5. I want to get statistics for employees on how many tickets they have done, how much they have accepted etc.

So it would be the maximal value for each ticket_id.

Database looks like this:

There is also time_stamp for each addition of a new row to the table.

Database sorted on ticket_id desc and status desc

I know that I have to count the max of status for each ticket_id but I don't really know how to make it properly.

select autor_of_changes, count(ticket_status) as statusiki from aa_ticketing_app_log 
group by autor_of_changes
order by statusiki desc, autor_of_changes desc

I had no other idea, but this one gives me an error :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

would love to get some help or tips on it, Thanks a lot!

CodePudding user response：

As "final statistics", we presented for every employee how many tickets are processed, classified by tickets type. Using 'Case...When' statement.

[Result]

[Code]

select  autor_of_changes, 
count(CASE WHEN ticket_status=0 then 1  END) as 'Received' ,
count(CASE WHEN ticket_status=1 then 1  END) as 'Accepted' ,
count(CASE WHEN ticket_status=2 then 1  end) as 'Done' ,
count(CASE WHEN ticket_status=5 then 1  END) as 'Declined',
COUNT(ticket_status) as 'All_tickets'

from aa_ticketing_app_log
group by autor_of_changes
order by All_tickets desc

CodePudding user response：

SELECT autor_of_changes
     , ticket_status
     , COUNT(ticket_status) OVER (PARTITION BY autor_of_changes, ticket_status 
                                  ORDER BY ticket_status DESC)
     , ROW_NUMBER() OVER(PARTITION BY autor_of_changes, ticket_status
                         ORDER BY ticket_status DESC) AS status_rank
FROM aa_ticketing_app_log
WHERE status_rank = 1
;