asked on

Combine two Oracel queries

Hi,

I just need a bit of help with the SQL. I have two queries, and would like to combine them into once query as efficiently as possible. The tables that I am working with are large (millions of rows) and the server is quite loaded. Any suggestions on how to improve the efficiency of these queries is appreciated, I don't know SQL very well.

Here are two queries, they are identical except that each one returns the totals if COLUMN_D = Option_1 vs Option_2.

What I'd like is to just have one query, and have the totals for Option_1 and Option_2 (both from column_D) split into two columns.

Thank you

SELECT  trunc(CREATION_DATE) CREATED_DATE,

    CASE 
        WHEN COLUMN_A = 'xxxxxxx'
            THEN 'x'
        WHEN COLUMN_A = 'yyyyyyy'
            THEN 'y'
        ELSE 'OTHER'
    END COLUMN_A,
    
COUNT (COLUMN_D) TOTAL_1         
FROM DB.TABLE_A 

Where trunc(SYSDATE) - 1 <trunc(CREATION_DATE)
and COLUMN_D = 'OPTION_2'

group by trunc(CREATION_DATE), COLUMN_A;

Open in new window

SELECT  trunc(CREATION_DATE) CREATED_DATE,

    CASE 
        WHEN COLUMN_A = 'xxxxxxx'
            THEN 'x'
        WHEN COLUMN_A = 'yyyyyyy'
            THEN 'y'
        ELSE 'OTHER'
    END COLUMN_A,
    
COUNT (COLUMN_D) TOTAL_1         
FROM DB.TABLE_A 

Where trunc(SYSDATE) - 1 <trunc(CREATION_DATE)
and COLUMN_D = 'OPTION_1'

group by trunc(CREATION_DATE), COLUMN_A;

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

Note I removed the TRUNC around creation_date in your where clause.

It's not needed and actually hurts performance if you have that column indexed

Sean Stuber

oops, missed midnight though

creation_date > TRUNC(SYSDATE)

should be

creation_date >= TRUNC(SYSDATE)

awking00

Is there only option_1 and option_2 or are there others?

Sean Stuber

if column_d is restricted to only those 2 strings then the where clause IN condition on them isn't needed
but it shouldn't hurt to have it either

TelMaco

ASKER

There are other options for COLUMN_D, so the IN statement is helpful. This works perfectly. Thank you!