Shawn
asked on
count different documents per user
In the query below I would like to have an extra column which counts the number of documents per user.
any ideas?
any ideas?
SELECT m.IMailID, m.ClientID, m.EnvelopeImagePath, m.EnvelopeImageStatus, m.EnvelopeImageName, u.UserID, u.FirstName, u.LastName, u.UserEmail,
m.EnvelopeAlertDate
FROM imail.tblIMail AS m INNER JOIN
imail.tblClients AS c ON m.ClientID = c.ClientID RIGHT OUTER JOIN
imail.tblUsers AS u ON c.ClientID = u.ClientID
WHERE (u.UserID NOT IN
(SELECT imail.tbliMailActions.UserID
FROM imail.tbliMailActions INNER JOIN
imail.tblIMail AS tblIMail_1 ON imail.tbliMailActions.IMailID = tblIMail_1.IMailID)) AND (m.EnvelopeAlertDate IS NULL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> Also, you are using a RIGHT OUTER JOIN, do you want all the records from tblImail even if the user does not exist, or do you want all the Users even if the tblImail does not exist ?
oops, fixed that.
code below gives me 4 for all users whereas in my data I should get 1 for each except one which is 2
oops, fixed that.
code below gives me 4 for all users whereas in my data I should get 1 for each except one which is 2
SELECT m.IMailID, m.ClientID, m.EnvelopeImagePath, m.EnvelopeImageStatus, m.EnvelopeImageName, u.UserID, u.FirstName, u.LastName, u.UserEmail,
m.EnvelopeAlertDate ,(select count(*) from imail.tblIMail where userID = u.userID) as documentCount
FROM imail.tblIMail AS m INNER JOIN
imail.tblClients AS c ON m.ClientID = c.ClientID INNER JOIN
imail.tblUsers AS u ON c.ClientID = u.ClientID
WHERE (u.UserID NOT IN
(SELECT imail.tbliMailActions.UserID
FROM imail.tbliMailActions INNER JOIN
imail.tblIMail AS tblIMail_1 ON imail.tbliMailActions.IMailID = tblIMail_1.IMailID)) AND (m.EnvelopeAlertDate IS NULL)
rainverse, you can't have all those columns in the select clause and only group by one of them to add an aggregate function; when grouping you need all columns to be either an aggregate or grouped
as a side note, the sub query looks like it must be wrong.. there is no reference to the main queries and it's joining to another table for no reason?
WHERE (u.UserID NOT IN
(SELECT imail.tbliMailActions.User ID
FROM imail.tbliMailActions
INNER JOIN imail.tblIMail AS tblIMail_1 ON imail.tbliMailActions.IMai lID = tblIMail_1.IMailID))
Could you replace it with this?
WHERE NOT EXists (SELECT act.UserID
FROM imail.tbliMailActions act
where act.user_id = u.user_id)
WHERE (u.UserID NOT IN
(SELECT imail.tbliMailActions.User
FROM imail.tbliMailActions
INNER JOIN imail.tblIMail AS tblIMail_1 ON imail.tbliMailActions.IMai
Could you replace it with this?
WHERE NOT EXists (SELECT act.UserID
FROM imail.tbliMailActions act
where act.user_id = u.user_id)
ASKER
got it. thx gdemaria:
SELECT m.IMailID, m.ClientID, m.EnvelopeImagePath, m.EnvelopeImageStatus, m.EnvelopeImageName, u.UserID, u.FirstName, u.LastName, u.UserEmail,
m.EnvelopeAlertDate ,(select count(*) from imail.tblIMail where ClientID = u.ClientID) as documentCount
FROM imail.tblIMail AS m INNER JOIN
imail.tblClients AS c ON m.ClientID = c.ClientID INNER JOIN
imail.tblUsers AS u ON c.ClientID = u.ClientID
WHERE (u.UserID NOT IN
(SELECT imail.tbliMailActions.UserID
FROM imail.tbliMailActions INNER JOIN
imail.tblIMail AS tblIMail_1 ON imail.tbliMailActions.IMailID = tblIMail_1.IMailID)) AND (m.EnvelopeAlertDate IS NULL)
Absolutely right. My bad.
> code below gives me 4 for all users whereas in my data I should get 1 for each except one which is 2
You need to change the join then...
do you have userId in the tblImail table?
,(select count(*) from imail.tblIMail IM where im.userID = u.userID) as documentCount
If not, then update the join so it's correct.
You are using the same table as your main select ... I assumed you were trying to count ANOTHER table.. I would do one of two things.. add a new query just to count or count in the code after the query from the records you've fetched...
You need to change the join then...
do you have userId in the tblImail table?
,(select count(*) from imail.tblIMail IM where im.userID = u.userID) as documentCount
If not, then update the join so it's correct.
You are using the same table as your main select ... I assumed you were trying to count ANOTHER table.. I would do one of two things.. add a new query just to count or count in the code after the query from the records you've fetched...
ASKER
>>Could you replace it with this?
WHERE NOT EXists (SELECT act.UserID
FROM imail.tbliMailActions act
where act.user_id = u.user_id)
right again gdemaria!
execution is much faster now :)
WHERE NOT EXists (SELECT act.UserID
FROM imail.tbliMailActions act
where act.user_id = u.user_id)
right again gdemaria!
execution is much faster now :)
SELECT m.IMailID, m.ClientID, m.EnvelopeImagePath, m.EnvelopeImageStatus, m.EnvelopeImageName, u.UserID, u.FirstName, u.LastName, u.UserEmail,
m.EnvelopeAlertDate ,(select count(*) from imail.tblIMail where ClientID = u.ClientID) as documentCount
FROM imail.tblIMail AS m INNER JOIN
imail.tblClients AS c ON m.ClientID = c.ClientID INNER JOIN
imail.tblUsers AS u ON c.ClientID = u.ClientID
WHERE NOT EXists (SELECT act.UserID
FROM imail.tbliMailActions act
where act.UserID = u.UserID )
ASKER
>> I would do one of two things.. add a new query just to count or count in the code after the query from the records you've fetched...
with the query I posted above would you still recommend adding another query or counting in the code?
with the query I posted above would you still recommend adding another query or counting in the code?
no, I take that back - I think it will work fine.
ASKER
ok, thanks again
Open in new window