How to count records from database and put them into List<Long>,each row represent by the lett-CodePudding

I need to take values from a database and put them into a List<Long>. I made a native query but it doesn't work correctly. Here's a picture of the column on my database:

I need to count the same statuses ("W" and "I" represent the same status but are represented by another letter). I prepared a query as shown below:

@Query(value = "SELECT COUNT(CASE WHEN table.status = 'W' OR  table.status = 'I' THEN 1 ELSE 0 END) AS WI,"
            " COUNT(CASE WHEN table.status = 'S' THEN 1 ELSE 0 END) AS S,"  
            " COUNT(CASE WHEN table.status = 'N' THEN 1 ELSE 0 END) AS N,"  
            " COUNT(CASE WHEN table.status = 'D' THEN 1 ELSE 0 END) AS D,"  
            " COUNT(CASE WHEN table.status = 'E' THEN 1 ELSE 0 END) AS E,"  
            " COUNT(CASE WHEN table.status = 'T' THEN 1 ELSE 0 END) AS T"  
            " FROM table.table table"  
            " WHERE table.id = :id" 
            " GROUP BY table.status"  
            " ORDER BY table.status ASC",
            nativeQuery = true)
    List<Long> query1 (Long id);

But it's not working correctly. When I run the query I get the following result:

But it should be {2(D),2(E),1(N),1(S),1(T),3(W and I). Do you have any idea on how to fix it?

CodePudding user response：

This query returns what you want:

select count(*) from (
    SELECT CASE WHEN status = 'W' OR  status = 'I' THEN 'WI' else status end as status
    FROM @table 
) as a
group by status
order by status

So maybe this is the code you require (you have very complex table names):

@Query(value = "select count(*) from ("   
            " SELECT CASE WHEN table.status = 'W' OR table.status = 'I' THEN 'WI' else table.status end as status"  
            " FROM table.table table "  
            " WHERE table.id = :id" 
            " ) as a "  
            " group by status"  
            " order by status",
            nativeQuery = true)
    List<Long> query1 (Long id);