Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle SQL Syntax for Aggregate Cp

Posted on 2011-09-27
9
Medium Priority
?
242 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
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 74

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 77

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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36711842
sorry.   I missed the different last name piece.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 74

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 74

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 74

Accepted Solution

by:
sdstuber earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

609 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