Oracle SQL Syntax for Aggregate Cp
select distinct dictatorid,dictatorlastname, count(dictatorid)
from ichart.transactions where
reportperiod = '201108'
group by dictatorid, dictatorlastname
order by dictatorid ASC;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!
Something like:
select dictatorid,dictatorlastnam
from ichart.transactions where
reportperiod = '201108'
group by dictatorid, dictatorlastname
having count(dictatorid) > 1
order by dictatorid ASC;
select dictatorid,dictatorlastnam
from ichart.transactions where
reportperiod = '201108'
group by dictatorid, dictatorlastname
having count(dictatorid) > 1
order by dictatorid ASC;
sorry. I missed the different last name piece.
ASKER
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!
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!
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;
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;
if this doesn't work, please provide some sample data that fails, along with expected results
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANK YOU!
FROM (SELECT dictatorid, dictatorlastname, COUNT(*) OVER (PARTITION BY dictatorid) cnt
FROM ichart.transactions
WHERE reportperiod = '201108')
WHERE cnt > 1
ORDER BY dictatorid ASC;