Solved

Query within query

Posted on 2011-03-02
1
357 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 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

690 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