I have the following table. Using sqlite
DB
Item | Result |
---|---|
A | Pass |
B | Pass |
A | Fail |
B | Fail |
I want to realize the above table as below using some query.
Item | Total | Accept | Reject |
---|---|---|---|
A | 2 | 1(50%) | 1(50%) |
B | 2 | 1(50%) | 1(50%) |
How should I construct this query?
Advertisement
Answer
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:
- Since
SQLITE
does not handlePIVOT
, we are creating the flags Pass and Fail manually usingCASE
statement - To calculate
total
,COUNT
is used as analytical function here. It is basically a shortcut to calculate count and place it in all rows - 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