Solved

Oracle SQL Syntax for Aggregate Cp

Posted on 2011-09-27
9
235 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 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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 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

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

Suggested Solutions

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

680 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