Solved

count different documents per user

Posted on 2011-09-12
12
342 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

Question has a verified solution.

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

Suggested Solutions

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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 …

756 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