SQL using Aggregate COUNT [message #315049] |
Fri, 18 April 2008 14:04  |
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 #315058 is a reply to message #315052] |
Fri, 18 April 2008 14:35   |
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 #315068 is a reply to message #315058] |
Fri, 18 April 2008 16:48   |
 |
Barbara Boehmer
Messages: 9104 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  |
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;
|
|
|