Select two different values from one row

Question

I want this table

Booking table

+----+--------+
| ID | Status |
+----+--------+
| 1  | R      |
| 2  | R      |
| 3  | C      |
| 4  | C      |
+----+--------+

To this total number of each

+----------+-----------+
| Reserved | Cancelled |
+----------+-----------+
| 2        | 2         |
+----------+-----------+

So far I got this -

SELECT Status AS Reserves 
FROM Booking WHERE Status = 'R' OR Status = 'C'

Output -

+----------+
| reserves |
+----------+
| R        |
| C        |
| R        |
| C        |
+----------+

This is called a pivot table and this question has been asked and answered on SO several times. — Shadow, May 04 '17 at 00:43
The duplicate topic shows how to create a static (columns are known in advance) and a dynamic (columns are not known in advance) in mysql. The static version can be used in postgresql as well. — Shadow, May 04 '17 at 00:46
See a postgresql specific solution as well: http://stackoverflow.com/questions/3002499/postgresql-crosstab-query — Shadow, May 04 '17 at 00:50

score 2 · Accepted Answer · answered May 04 '17 at 00:43

2

Put the logic from your WHERE clause into a CASE expression, and sum it over the entire table:

SELECT
    SUM(CASE WHEN Status = 'R' THEN 1 END) AS Reserved,
    SUM(CASE WHEN Status = 'S' THEN 1 END) AS Cancelled
FROM Booking

answered May 04 '17 at 00:43

Tim Biegeleisen

1 Answers1