CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperplsqldeveloper
Rose
Rose

Reputation: 21

How to write sql SELECT query to for this problem?

Consider the 2 below tables

DETAILS table:-

email    Name    Region   PostalCode
[email protected]  Mike    US-east    1234
[email protected]  Sara    US-east    2341
[email protected]  Sara    US-west    1234
[email protected]  Ash     US-west    6542
[email protected]  Cindy   US-west    4213
[email protected]  George  US-east    1234
[email protected]  Thomas  US-east    3412

EMAIL_STATUS table:-

email    status  
[email protected]  In progress     
[email protected]  Resolved

I require a query that left joins the tables(left is DETAILS table and right is EMAIL_STATUS table) and fetches only one row for each email

expected result:-

email    Name    Region   PostalCode  status        count(*)
[email protected]  Mike    US-east    1234  In progress       3
[email protected]  Ash     US-west    6542  null              2
[email protected]  George  US-east    1234  Resolved          2

I tried various queries but none seem to work in order to get the result i expect as it leads to errors due to group by.

Upvotes: 0

Views: 92

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

This seems like a left join with group by:

select d.email, min(d.name) as name, min(d.region) as region,
       min(d.postalcode) as postalcode,
       es.status, count(*) as cnt
from details d left join
     email_status es
     on d.email = es.email
group by d.email, es.status;

The above returns one row for each email. If you want a particular row, then use row_number() and count(*):

select d.*,
       es.status, d.cnt
from (select d.*,
             count(*) over (partition by email) as cnt,
             row_number() over (partition by email order by ?) as seqnum  -- the ? describes which row you want
      from details d
     ) d left join
     email_status es
     on d.email = es.email;

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

Here's a slightly different approach, using LAG to determine if you're looking at the first line for a particular email address:

WITH DETAIL_COUNT AS (SELECT EMAIL, COUNT(*) AS EMAIL_COUNT
                        FROM DETAILS
                        GROUP BY EMAIL),
     ALL_ROWS AS (SELECT d.EMAIL,
                         d.NAME,
                         d.REGION,
                         d.POSTAL_CODE,
                         e.STATUS,
                         dc.EMAIL_COUNT,
                         LAG(d.EMAIL, 1) OVER (ORDER BY d.EMAIL, d.NAME) AS PREV_EMAIL
                    FROM DETAILS d
                    LEFT OUTER JOIN EMAIL_STATUS e
                      ON e.EMAIL = d.EMAIL
                    LEFT OUTER JOIN DETAIL_COUNT dc
                      ON dc.EMAIL = d.EMAIL
                    ORDER BY d.EMAIL, d.NAME)
SELECT EMAIL, NAME, REGION, POSTAL_CODE, STATUS, EMAIL_COUNT
  FROM ALL_ROWS
  WHERE PREV_EMAIL IS NULL OR
        PREV_EMAIL <> EMAIL

This produces the results:

EMAIL   NAME    REGION  POSTAL_CODE STATUS      EMAIL_COUNT
[email protected] Mike    US-east 1234        In progress 3
[email protected] Ash     US-west 6542                    2
[email protected] George  US-east 1234        Resolved    2

which I believe is what you're looking for.

Upvotes: 1

Prabhat-VS
Prabhat-VS

Reputation: 1530

  Select d.email, min(d.name) As name, min(d.region) as region,
         min(d.postalcode) as postalcode,
         es.status, count(*) As cnt
    From details d  
    Join email_status es
      On d.email = es.email
Group By d.email;

Upvotes: 0

Related Questions