?
Solved

Oracle SQL Syntax for Aggregate Cp

Posted on 2011-09-27
9
Medium Priority
?
244 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 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 78

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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36711842
sorry.   I missed the different last name piece.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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. …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

850 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