CODEWITHSUNDEEP
sqloracle-database
JD2775
JD2775

Reputation: 3801

Combining count/max Select conditions into one

I am trying to combine both these queries into one, so I have one select query to return one row with the table count and max date. I can't figure out an easy way to do that. Any ideas? Thanks

select count(*) from ods.ADDRESS

select max(last_modified)
from
(
select
CASE WHEN MODIFY_DT IS NULL THEN CREATE_DT
ELSE MODIFY_DT END AS LAST_MODIFIED
FROM ODS.ADDRESS
)

Upvotes: 1

Views: 40

Answers (3)

TheWildHealer
TheWildHealer

Reputation: 1616

Well, simply combine them :D

select count(*), max(
CASE WHEN MODIFY_DT IS NULL THEN CREATE_DT
ELSE MODIFY_DT END ) AS LAST_MODIFIED
  from ods.ADDRESS

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Conditional aggregation is one way to do this:

select COUNT(*), 
       MAX(CASE WHEN MODIFY_DT IS NULL 
                THEN CREATE_DT
                ELSE MODIFY_DT 
           END) AS LAST_MODIFIED
from ODS.ADDRESS a;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Here is one option:

SELECT
    COUNT(*) AS cnt,
    MAX(CASE WHEN MODIFY_DT IS NULL THEN CREATE_DT ELSE MODIFY_DT END) AS LAST_MODIFIED
FROM ODS.ADDRESS;

Note that you don't even need a subquery. We can also try simplifying the above using COALESCE:

SELECT
    COUNT(*) AS cnt,
    MAX(COALESCE(MODIFY_DT, CREATE_DT)) AS LAST_MODIFIED
FROM ODS.ADDRESS;

Upvotes: 3

Related Questions