CODEWITHSUNDEEP
sqloracle-databaseselectsum
Ove Tombak
Ove Tombak

Reputation: 85

How to select from a table with additional where clause on a single column

I'm having trouble formulating a SQL query in Oracle. Here's my sample table:

+----+-----------+-----------+--------+
| id | start     | end       | number |
+----+-----------+-----------+--------+
| 1  | 21-dec-19 | 03-jan-20 | 12     |
| 2  | 23-dec-19 | 05-jan-20 | 10     |
| 3  | 02-jan-20 | 15-jan-20 | 9      |
| 4  | 09-jan-20 | NULL      | 11     |
+----+-----------+-----------+--------+

And here's what I have so far:

SELECT
    SUM(number) AS total_number,
    SUM(number) AS total_ended_number -- (WHERE end IS NOT NULL)
FROM table
WHERE ... -- a lot of where clauses

And the desired result:

+--------------+--------------------+
| total_number | total_ended_number |
+--------------+--------------------+
| 42           | 31                 |
+--------------+--------------------+

I understand I could do a separate select inside 'total_ended_number', but the initial select has a bunch of where clauses already which would need to be applied to the internal select as well.

I'm capable of formulating it in 2 separate selects or 2 nested selects with all the where clauses duplicated, but my intended goal is to not duplicate the where clauses that would both be used on the table.

Upvotes: 2

Views: 70

Answers (2)

Mureinik
Mureinik

Reputation: 312259

You could sum over a case expression with this logic:

SELECT
    SUM(number) AS total_number,
    SUM(CASE WHEN end IS NOT NULL THEN number END) AS total_ended_number
FROM table
WHERE ... -- a lot of where clauses

Upvotes: 1

juergen d
juergen d

Reputation: 204924

SUM(case when "end" is not null then number else 0 end) AS total_ended_number

Upvotes: 1

Related Questions