Solved

count different documents per user

Posted on 2011-09-12
12
337 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AWS New EC2 Instance and EBS Storage 2 70
Connect to MS-SQL server from Linux/PHP 8 81
change time in cron 4 65
ColdFusion Need assistance updating my legacy zip function 13 35
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

947 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

21 Experts available now in Live!

Get 1:1 Help Now