Alexander Momchliov - 1 year ago 68
SQL Question

# Combine 3 column sums into a single table

I have a simple database to simulate a movie rental service. I have the following 3 select queries, which work perfectly fine on their own:

1. Calculate all revenue made from rental prices:

``````SELECT SUM(PRICE) AS RENTAL_REVENUE
FROM RENTAL
``````

output:

``````    +----------------+
| RENTAL_REVENUE |
+----------------+
|      39.92     |
+----------------+
``````

2. Calculate all revenue made from paid late fees:

``````SELECT SUM(NULLIF(LATEFEE, 0)) AS PAID_LATE_FEES
FROM RENTAL
WHERE RETURNED = 1
``````

output:

``````    +----------------+
| PAID_LATE_FEES |
+----------------+
|       2.99     |
+----------------+
``````

3. Calculate the sum of all yet unpaid late fees:

``````SELECT SUM(NULLIF(LATEFEE, 0)) AS OUTSTANDING_LATE_FEES
FROM RENTAL
WHERE RETURNED = 0
``````

output:

``````    +-----------------------+
| OUTSTANDING_LATE_FEES |
+-----------------------+
|          5.98         |
+-----------------------+
``````

I would like to combine the results of these 3 queries into a table with 3 columns, like so:

``````+----------------+----------------+-----------------------+
| RENTAL_REVENUE | PAID_LATE_FEES | OUTSTANDING_LATE_FEES |
+----------------+----------------+-----------------------+
|      39.92     |       2.99     |          5.98         |
+----------------+----------------+-----------------------+
``````

I was able to achieve this with the following query, but the
`1 = 1`
nonsense suggests to me that there might be a better way.

``````SELECT rental_revenue + paid_late_fees + outstanding_late_fees AS TOTAL_REVENUE,
rental_revenue,
paid_late_fees,
outstanding_late_fees
FROM   (SELECT SUM(price) AS RENTAL_REVENUE
FROM   rental)
inner join (SELECT SUM(Nullif(latefee, 0)) AS PAID_LATE_FEES
FROM   rental
WHERE  returned = 1)
ON 1 = 1
inner join (SELECT SUM(Nullif(latefee, 0)) AS OUTSTANDING_LATE_FEES
FROM   rental
WHERE  returned = 0)
ON 1 = 1;
``````

Is there a better way?

Answer Source

You can select all records and build a filter inside the `SUM` using `CASE-WHEN` or `DECODE`:

``````SELECT SUM(price) AS rental_revenue,
SUM(CASE
WHEN returned = 1 AND latefee <> 0 THEN latefee
ELSE 0
END) AS paid_late_fees,
SUM(CASE
WHEN returned = 0 AND latefee <> 0 THEN latefee
ELSE 0
END) AS outstanding_late_fees
FROM rental
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download