Alexander Momchliov Alexander Momchliov - 1 month ago 8
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

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
Comments