Solved

count different documents per user

Posted on 2011-09-12
12
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion Complex Structure Reference 4 50
REGEX HELP 11 61
Coldusion - DATA insert syntax problem 12 72
Customising IE behaviour on certain pages 2 98
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…

739 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