Count queries and subquery question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count queries and subquery question

Author

Topic

tech1
Starting Member

49 Posts

Posted - 2010-06-16 : 10:43:33

Trying combine multiple tables based on this query:

headings:

Company Name
Contact Name
Last login date
No of times logged in
Pages viewed
No. of downloads
No of requests submitted

Anything relating to "Company" is in the - company table (companyID)
Everything else is in the "Audit" table.

The Audit table has an auditTypeID (AuditType being login, pages viewed, downloads, requests submitted)

the AuditTable has userID
the User table has UserID and companyID

so for a company, and for each user within that company, I want the above details for that user for a given companyID

how would I create this query?

tech1
Starting Member

49 Posts

Posted - 2010-06-16 : 11:34:33

this kind of works

quote:

SELECT comp.CompanyName, u.ContactName,
(SELECT TOP 1 a0.createdOn FROM Audit a0 WHERE
a0.userId = u.userId AND a0.auditTypeId = 0 ORDER BY a0.createdOn DESC) AS 'LastLogonDate',

(SELECT COUNT(a1.[auditID]) FROM Audit a1
WHERE a1.auditTypeId = 0 AND a1.userId = u.UserID) AS 'NoOfTimesLoggedIn',

(SELECT COUNT(a2.[auditID]) FROM Audit a2
WHERE a2.auditTypeId = 2 AND a2.userId = u.userId) AS 'PagesViewed',

(SELECT COUNT(a3.[auditID]) FROM Audit a3
WHERE a3.auditTypeId = 1 AND a3.userId = u.userId) AS 'NoOfDownloads',

(SELECT COUNT(a4.[auditID]) FROM Audit a4
WHERE a4.auditTypeId = 3 AND a4.userId = u.userId) AS 'NoOfRequestSubmitted'
FROM [User] u

INNER JOIN Company comp ON
comp.companyId = u.companyId
INNER JOIN Audit a ON
a.userId = u.userId

WHERE comp.companyId = @paramHere

except, shows ALOT of duplicate records but the correct values I think - something im doing wrong here but no idea what, doing a DISTINCT does work correctly but the query I am doing wrong somewhere

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-18 : 12:29:12

[code]
SELECT COUNT(CASE WHEN a1.auditTypeId = 0 THEN a1.[auditID] ELSE NULL END) AS [NoOfTimesLoggedIn],
COUNT(CASE WHEN a1.auditTypeId = 2 THEN a1.[auditID] ELSE NULL END) AS [PagesViewed],
COUNT(CASE WHEN a1.auditTypeId = 1 THEN a1.[auditID] ELSE NULL END) AS [NoOfDownloads],
COUNT(CASE WHEN a4.auditTypeId = 3 THEN a1.[auditID] ELSE NULL END) AS [NoOfRequestSubmitted],
u1.createdOn AS LastLogonDate
FROM [User] u
INNER JOIN Company comp ON
comp.companyId = u.companyId
INNER JOIN Audit a ON
a.userId = u.userId
OUTER APPLY (SELECT TOP 1 a0.createdOn FROM Audit a0 WHERE
a0.userId = u.userId AND a0.auditTypeId = 0 ORDER BY a0.createdOn DESC)u1
WHERE comp.companyId = @paramHere
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources