• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Query within query

I have two tables
1. PERSON with person_id and name
2. PERSON_DETAILS with person_id and login time

I have also listed the query and current/expected results there. Can anyone tell me how do i modify the query to get the expected output?

PERSON

PERSON_SK    PERSON_NAME

1000         John
1001         Mat
1002         David


PERSON_DETAILS

PERSON_SK   LOGIN_TS

1000        01/01/2011
1000        02/01/2011
1000        03/01/2011
1001        01/01/2011
1001        02/01/2011
1002        01/01/2011

Query

SELECT  PERSON_SK count(*) from PERSON_DETAILS where LOGIN_TS BETWEEN  
TO_DATE('01/01/2011', 'MM/DD/YYYY')
AND TO_DATE('03/04/2011', 'MM/DD/YYYY')
GROUP BY PERSON_SK
ORDER BY count(*) DESC

Current Output:

PERSON_SK         COUNT(*)
1000              3
1001              2
1002              1

Expected

PERSON         COUNT(*)
John                     3
Mat                      2
David              1
0
johnportaldev
Asked:
johnportaldev
1 Solution
 
slightwv (䄆 Netminder) Commented:
simple join?

SELECT  PERSON, count(*) from PERSON_DETAILS pd, person p where pd.person_sk=p.person_sk and LOGIN_TS BETWEEN  
TO_DATE('01/01/2011', 'MM/DD/YYYY')
AND TO_DATE('03/04/2011', 'MM/DD/YYYY')
GROUP BY pd.PERSON_SK
ORDER BY count(*) DESC

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now