Solved

Oracle SQL Syntax for Aggregate Cp

Posted on 2011-09-27
9
231 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
Comment Utility
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)
Comment Utility
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)
Comment Utility
sorry.   I missed the different last name piece.
0
 

Author Comment

by:codefinger
Comment Utility
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
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.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
if this doesn't work,  please provide some sample data that fails,  along with expected results
0
 

Author Comment

by:codefinger
Comment Utility
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
Comment Utility
SELECT   dictatorid, dictatorlastname, cnt
...

order by cnt desc
0
 

Author Closing Comment

by:codefinger
Comment Utility
THANK YOU!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now