?
Solved

Query within query

Posted on 2011-03-02
1
Medium Priority
?
360 Views
Last Modified: 2012-05-11
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
Comment
Question by:johnportaldev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35022028
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

Featured Post

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question