CODEWITHSUNDEEP
sqlamazon-redshift
Tyler Prichard
Tyler Prichard

Reputation: 3

Applying CASE WHEN to MIN/MAX

I'm trying to apply MIN/MAX to my SELECT clause based on two criteria. (See Below). When I try to reference my column submitted_at in another query, it says the column reference is ambiguous. 

MIN(CASE WHEN (document != 'dphoto' AND document_type != 'dlicenses' AND 
status = 'upload') THEN occurred_at END) AS submitted_at,`

MAX(CASE WHEN (document_type = 'dphoto' AND document_type = 'dlicenses' AND
status = 'upload') THEN occurred_at END) AS submitted_at`

I've attempted to consolidate the phrase into 1 select but have been unsuccessful. Any tips are appreciated

Upvotes: 0

Views: 5471

Answers (1)

Siyual
Siyual

Reputation: 16917

You can combine the two into one column by using the following:

CASE    WHEN    (document != 'dphoto' AND document_type != 'dlicenses' AND status = 'upload')
            THEN    Min(occurred_at)
        WHEN    (document_type = 'dphoto' AND document_type = 'dlicenses' AND status = 'upload') 
            THEN    Max(occurred_at)
END As Submitted_at

Upvotes: 1

Related Questions