How to count each value of a column in a table?

Question

I have a table like this:

UserID     Customer ID status        
1               1          1
1               2          1 
1               3          1
1               4          2
1               5          1
1               6          3
1               7          2
2               8          1
2               9          2 
 ........

I want to summarize this table, to this:

 UserID           count(status 1)    count(status 2)   count(status 3)
    1                4                2                     1 
    2                1                2                     3
   .........

How can I do that in PL/SQL?

Thank in advance

Answer 1

You can group on UserId and sum up the different status codes.

Something like:

select
  UserId,
  sum(case status when 1 then 1 else 0 end) as Status1,
  sum(case status when 2 then 1 else 0 end) as Status2,
  sum(case status when 3 then 1 else 0 end) as Status3
from SomeTable
group by UserId
order by UserId

You might also consider simply grouping on UserId and status, although the result is of course differently laid out:

select UserId, status, count(*)
from SomeTable
group by UserId, status
order by UserId, status

Answer 2

select userid, 
       count(decode(status, 1, 1, null)),
       count(decode(status, 2, 1, null)),
       count(decode(status, 3, 1, null)),
  from table
 group by userid

Answer 3

SELECT *
  FROM ( SELECT UserID,
                status,
                COUNT(status)
           FROM <table>
          GROUP BY UserID,
                   status
       )
 PIVOT(COUNT(status) FOR status IN (1,2,3))

Answer 4

Just to follow up @Vimvq1987 and @Guffa comments: the correct syntax for SQL is case ... end, but for PL/SQL it sould be case ... end case, so the information on the link you've provided is right.

Hence in your SQL queries (either you execute it in SQL-Plus or in the DML in PL/SQL) you should use case ... end, but in PL/SQL routines case ... end case is required.