Solved

Query within query

Posted on 2011-03-02
1
353 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
exp/imp 25 73
Retreiving column names in Windows but not in Unix 11 45
convert in derived column 7 30
How can I update a 2nd table with what is inserted into the 1st? 5 35
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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