I have the following table. Using sqlite DB

I want to realize the above table as below using some query.

How should I construct this query?

CodePudding user response：

You can try PIVOT() if your DBMS supports. Then use CONCAT or || operator depending on the DMBS.

Query:

SELECT 
    item, 
    total, 
    SUM(Pass)||'('|| CAST((SUM(Pass)*1.0/total*1.0)*100.0 AS DECIMAL)||'%)' AS Accept, 
    SUM(Fail)||'('|| CAST((SUM(Fail)*1.0/total*1.0)*100.0 AS DECIMAL)||'%)' AS Reject
FROM 
    (
     SELECT 
         Item, 
         result,
         COUNT(result) OVER(PARTITION BY item ORDER BY result ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total,
         CASE
           WHEN Result = 'Pass' then 1
           ELSE 0
         END AS Pass,
         CASE
           WHEN Result = 'Fail' then 1
           ELSE 0
         END AS Fail
     FROM t
    ) AS j
GROUP BY item, total

Query explanation:

1. Since SQLITE does not handle PIVOT, we are creating the flags Pass and Fail manually using CASE statement
2. To calculate total, COUNT is used as analytical function here. It is basically a shortcut to calculate count and place it in all rows
3. Then in the outer query, we are calculating %s and using || as the concatenate operator to concatenate the result with total sum and % of it

See demo in db<>fiddle