SQL row in column

Question

I have this SQL query

SELECT COUNT(*)*100/(SELECT COUNT(*) FROM tickets WHERE status = 'closed')
FROM tickets 
WHERE closed_at <= due_at 
UNION 
SELECT COUNT(*)*100/(SELECT COUNT(*) FROM tickets WHERE status = 'closed') 
FROM tickets 
WHERE closed_at > due_at;

and it returns this

ROW 1 - 35
ROW 2 - 47

but I need the return like this:

 1 | 2 |
35  47

I need the returns in columns, not rows.

Thanks.

This was asked too many times, just search for PIVOT, UNPIVOT SQL TABLES — jean, Dec 05 '17 at 18:59
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — jean, Dec 05 '17 at 19:03

score 2 · Answer 1 · answered Dec 05 '17 at 19:01

Use conditional aggregation. I would recommend:

SELECT (SUM(CASE WHEN closed_at <= due_at THEN 100.0 ELSE 0 END) /
        SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END)
       ), 
       (SUM(CASE WHEN closed_at > due_at THEN 100.0 ELSE 0 END) /
        SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END)
       )
FROM tickets ;

It seems strange that you are filtering on status = 'closed' in the denominator, but not in the numerator. If status = closed should be the filter for both, then you can simplify this to:

SELECT AVG(CASE WHEN closed_at <= due_at THEN 100.0 ELSE 0 END),
       AVG(CASE WHEN closed_at > due_at THEN 100.0 ELSE 0 END)
FROM tickets
WHERE status = 'closed';

SQL row in column

1 Answers1