Solved

count different documents per user

Posted on 2011-09-12
12
340 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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