Solved

Query within query

Posted on 2011-03-02
1
354 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
1 Comment
 
LVL 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 39
performance query 4 32
Error querying database link from Oracle 10g to Postgresql 7 63
SQL Syntax 14 34
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

809 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