2
 ticket_number |          datetime          | status
---------------+----------------------------+---------
             1 | 2020-08-03 03:52:58.048196 | replied
             1 | 2020-08-02 19:55:49.121455 | new
             2 | 2020-07-30 03:52:58.048196 | pending
             2 | 2020-07-28 20:15:41.213842 | replied
             2 | 2020-07-26 03:52:58.482911 | new
             3 | 2020-06-17 19:55:49.394628 | closed
             3 | 2020-06-14 03:52:58.513141 | replied
             3 | 2020-06-11 19:55:49.242859 | new
             4 | 2020-05-14 07:13:50.527481 | new
             5 | 2020-05-13 11:24:38.558921 | new

The above table ordered by datetime. After ordering I need to group by ticket_number so that each ticket appears only once, and pick the status for the latest datetime per ticket. Then I need to count rows for each status.

Ticket 1 would have a status of replied - replied_count = 1,
Ticket 2 would have a status of pending - pending_count = 1,
Ticket 3 would have a status of closed - closed_count = 1,
Ticket 4 would have a status of new - new_count = 1,
Ticket 5 would have a status of mew - new_count = 2

Expected Result:

replied_count  |  pending_count  |  closed_count  |  new_count
---------------+-----------------+----------------+------------
             1 |               1 |              1 |          2
1

Use

WITH cte AS ( SELECT DISTINCT
                     ticket_number, 
                     FIRST_VALUE(status) OVER (PARTITION BY ticket_number 
                                               ORDER BY datetime DESC) last_status 
              FROM test )
SELECT last_status, COUNT(last_status) 
FROM cte
GROUP BY last_status

fiddle

for to obtain needed statistic in vertical form. If you need pivotted output strictly then use CROSSTAB() or similar, or emulate it using 4 CASEs.

  • This is brilliant. I tried so many different ways before coming here and wasted hours of time, and your solution far outperforms what I was doing (multiple subqueries with multiple inner joins). Love this thank you! – Bruce Aug 3 '20 at 19:04
0

This should be faster, yet:

SELECT status, count(*)
FROM  (
   SELECT DISTINCT ON (ticket_number) status
   FROM   test
   ORDER  BY ticket_number, datetime DESC
   ) sub
GROUP  BY 1;

db<>fiddle here

Assuming datetime is defined NOT NULL.
See:

If there are many rows per ticket, one of these techniques will be (much) faster, yet:

A table ticket with one row per relevant ticket would help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.