Home » SQL & PL/SQL » SQL & PL/SQL » SQL using Aggregate COUNT
SQL using Aggregate COUNT [message #315049] Fri, 18 April 2008 14:04 Go to next message
jrobertson
Messages: 3
Registered: April 2008
Location: North Carolina
Junior Member
I thought this would be easy, but it's not turning out that way.
I have 2 DB tables, a User table and and a Personnel table. The User table contains a userID, password, etc. The Personnel table contains names, addresses, etc. The User table has a foreign key to the Personnel table.
All users can edit their personal data in the User table, but some privileged users can alter data for other Users. I want to create a SQL query that identifies these privileged users have changed the User data for other users, and a count of how many User records have been changed.
I can do this with the 2 SQL commands below and merging the results by hand, but I'm trying to create a single SQL command. This SQL command gives the right count of Users modified:
SELECT last_modified_by, COUNT(last_modified_by)
FROM emtp.pf_user
HAVING COUNT(last_modified_by) > 1
GROUP BY last_modified_by
ORDER BY COUNT(last_modofied_by) DESC;
This SQL command gives the names of the privileged Users above:
SELECT p.lastname, p.firstname, u.userid
FROM emtp.pf_user u
INNER JOIN emtp.personnelbio p
ON u.personnelbioid = p.personnelbioid
WHERE u.userid IN
(SELECT last_modified_by FROM emtp.pf_user
HAVING COUNT(u.last_modified_by) > 1
GROUP BY last_modified_by)
ORDER BY userid;
Note that "last_modified_by" is the userid of the priv User.
I've tried the SQL below, but the count I get is always "1":
SELECT p.lastname, p.firstname, u.userid, count(u.last_modified_by) FROM emtp.pf_user u
INNER JOIN emtp.personnelbio p
ON u.personnelbioid = p.personnelbioid
WHERE u.userid IN
(SELECT last_modified_by FROM emtp.pf_user
HAVING COUNT(last_modified_by) > 1
GROUP BY last_modified_by)
GROUP BY p.lastname, p.firstname, u.userid
ORDER BY COUNT(u.last_modified_by);
Any ideas on what I am doing wrong here?

Re: SQL using Aggregate COUNT [message #315052 is a reply to message #315049] Fri, 18 April 2008 14:12 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Without any formatting,without any sample data,without any sample output,how can you think anyone in this weekend would answer your query?

FOrmat your code here
Re: SQL using Aggregate COUNT [message #315058 is a reply to message #315052] Fri, 18 April 2008 14:35 Go to previous messageGo to next message
jrobertson
Messages: 3
Registered: April 2008
Location: North Carolina
Junior Member
Good point.
Here is sample output from my 1st SQL query for the counts:
LAST_MODIFIED_BY COUNT(LAST_MODIFIED_BY)
---------------- -----------------------
userid1 1436
userid2 492
userid3 380
Here is sample output from my 2nd SQL query for the User info:
LASTNAME FIRSTNAME USERID
-------- --------- -------
user1lastname user1firstname userid1
user2lastname user2firstname userid2
user3lastname user3firstname userid3
And here is the output when I try to combine the SQL commands:
LASTNAME FIRSTNAME USERID COUNT(LAST_MODIFIED_BY)
-------- --------- ------- -----------------------
user1lastname user1firstname userid1 1
user2lastname user2firstname userid2 1
user3lastname user3firstname userid3 1

Here is the structure of each table (relevant columns only):
PF_USER
-------
userid VARCHAR2(60 bytes)
passwd VARCHAR2(60 bytes)
last_modified_by VARCHAR2(60 bytes)
personnelbioid INTEGER (foreign key)
PERSONNELBIO
------------
personnelbioid INTEGER (primary key)
lastname VARCHAR2(30 bytes)
firstname VARCHAR2(30 bytes)
address VARCHAR2(60 bytes)
email VARCHAR2(30 bytes)
Re: SQL using Aggregate COUNT [message #315059 is a reply to message #315058] Fri, 18 April 2008 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you will get benefit of reading OraFAQ Forum Guide.

Regards
Michel
Re: SQL using Aggregate COUNT [message #315068 is a reply to message #315058] Fri, 18 April 2008 16:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Just looking at the results of your two queries, you should be able to user your two good queries as inline views, join them by the common column, and select what you want, like below. Since the code is not formatted and there are no create table and insert statements and so on, I didn't look at it to see if the queries could be simplified or if it could be done without subqueries.

SELECT t2.lastname, t2.firstname, t2.userid, t1.alias_for_the_count
FROM   (first query with alias_for_the_count) t1, 
       (second query) t2
WHERE  t1.userid = t2.userid
/


Re: SQL using Aggregate COUNT [message #315211 is a reply to message #315068] Sat, 19 April 2008 23:50 Go to previous message
jrobertson
Messages: 3
Registered: April 2008
Location: North Carolina
Junior Member
Thank you for your response, the idea of inline views did not occur to me. Your code works fine, with one small change. View T1 does not contain "userid", so I can't join using "t1.userid" in the WHERE clause. I used "t1.last_modified_by" instead, and the query works fine.
Actually before I saw your post, I solved my own problem a different way. I used an INNER JOIN on the "last_modified_by" and "userid" columns in the same PF_USER table, I just used two different aliases for the PF_USER table:
select p.lastname, p.firstname, u1.userid, count(u2.last_modified_by) from emtp.pf_user u1
inner join emtp.pf_user u2
on u1.userid = u2.last_modified_by 
inner join emtp.personnelbio p
on u1.personnelbioid = p.personnelbioid 
where u1.userid in 
(select last_modified_by from emtp.pf_user 
having count(last_modified_by) > 1
group by last_modified_by) 
group by p.lastname, p.firstname, u1.userid 
order by count(u2.last_modified_by) desc; 
Previous Topic: Stored procdure for taking flat files data and storing in oracle table
Next Topic: DBMS_MVIEW.REFRESH_ALL_MVIEWS
Goto Forum:
  


Current Time: Sun Dec 11 00:07:48 CST 2016

Total time taken to generate the page: 0.20556 seconds