Solved

count different documents per user

Posted on 2011-09-12
12
336 Views
Last Modified: 2013-12-24
In the query below I would like to have an extra column which counts the number of documents per user.

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)

Open in new window

0
Comment
Question by:Shawn
  • 5
  • 5
  • 2
12 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 36524828

Without knowing the table name or columns, here is an example...

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 ?


SELECT  m.IMailID
   , m.ClientID
   , m.EnvelopeImagePath
   , m.EnvelopeImageStatus
   , m.EnvelopeImageName
   , u.UserID
   , u.FirstName
   , u.LastName
   , u.UserEmail
   , m.EnvelopeAlertDate
   ,(select count(*) from tblDocuments where userID = u.userID) as documentCount
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

Open in new window

0
 
LVL 5

Expert Comment

by:Rainverse
ID: 36524851
Try this...

SELECT		m.IMailID, m.ClientID, m.EnvelopeImagePath, m.EnvelopeImageStatus, m.EnvelopeImageName, 
			u.UserID, u.FirstName, u.LastName, u.UserEmail, m.EnvelopeAlertDate, COUNT(m.IMailID) AS 'NumDocs'
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)
GROUP BY u.UserID

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
ID: 36524887
>> 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


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)

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36524890
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
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36524919
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.UserID
                            FROM          imail.tbliMailActions
                           INNER JOIN imail.tblIMail AS tblIMail_1 ON imail.tbliMailActions.IMailID = 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)
0
 
LVL 1

Author Comment

by:Shawn
ID: 36524935
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)

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:Rainverse
ID: 36524937
Absolutely right.  My bad.  
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36524946
> 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...

0
 
LVL 1

Author Comment

by:Shawn
ID: 36524996
>>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 :)
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 )

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
ID: 36526050
>> 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?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36526140
no, I take that back - I think it will work fine.
0
 
LVL 1

Author Comment

by:Shawn
ID: 36526157
ok, thanks again
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now