Solved

Oracle SQL Syntax for Aggregate Cp

Posted on 2011-09-27
9
233 Views
Last Modified: 2012-05-12
select distinct dictatorid,dictatorlastname, count(dictatorid) 
from ichart.transactions where
reportperiod = '201108'
group by dictatorid, dictatorlastname
order by dictatorid ASC;

Open in new window


Can someone tell me how to modify this Oracle query so it only returns records where the total of each distinct dictatorid is greater than 1?   I am trying to identify dictatorid's that have been assigned to more than one last name and what last names they are assigned to.

Thanks!
0
Comment
Question by:codefinger
  • 4
  • 3
  • 2
9 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 36711831
SELECT   dictatorid, dictatorlastname
    FROM (SELECT dictatorid, dictatorlastname, COUNT(*) OVER (PARTITION BY dictatorid) cnt
            FROM ichart.transactions
           WHERE reportperiod = '201108')
   WHERE cnt > 1
ORDER BY dictatorid ASC;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36711835
Something like:

select dictatorid,dictatorlastname, count(dictatorid)
from ichart.transactions where
   reportperiod = '201108'
group by dictatorid, dictatorlastname
having count(dictatorid) > 1
order by dictatorid ASC;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36711842
sorry.   I missed the different last name piece.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:codefinger
ID: 36712127
None of these answers is quite doing it for me.

The way I see it, if any dictatorid shows up at all it should show up at least twice,
ex:  1800NOC     SMITH        2
       1800NOC     JONES       2

But with the examples so far I am getting dictatorids that show up only once with only one of the last names that are assigned to it.

Please try again!

Thanks!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36712149
I made the assumption that a given dictator/name combo was unique.

my original query should have picked up what you have shown
but could have also given false-postives on

1, codefinger
1, codefinger

as that is a count of 2 for that id,

this will handle duplicate names on a single id  and will ignore the above


SELECT   dictatorid, dictatorlastname
    FROM (SELECT dictatorid,
                 dictatorlastname,
                 COUNT(DISTINCT dictatorlastname) OVER (PARTITION BY dictatorid) cnt
            FROM transactions
           WHERE reportperiod = '201108')
   WHERE cnt > 1
ORDER BY dictatorid ASC;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36712156
if this doesn't work,  please provide some sample data that fails,  along with expected results
0
 

Author Comment

by:codefinger
ID: 36712227
sdstuber,

That seems to work pretty well when I add DISTINCT to the first line.  I can probably get away with that, but it would be really cool if I could also have a column that counted the distinct instances so I could sort by it.....so the most over-used dictatorid's could be at the top....

Care to give it a try?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36712360
SELECT   dictatorid, dictatorlastname, cnt
...

order by cnt desc
0
 

Author Closing Comment

by:codefinger
ID: 36712638
THANK YOU!
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
How can I update a 2nd table with what is inserted into the 1st? 5 35
export sql results to csv 6 35
Email Header Detail 12 55
Loops and updating in SQL Query 9 25
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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