Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

tsql query for counting a 'grouped by' and a where clause Expand / Collapse
Author
Message
Posted Wednesday, August 7, 2013 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
Can anyone help with this please?
I am trying to count all users in a table who are superusers (only if there is more than 1 super user).

TABLE: 'SuperUs'
FIELDS: 'Role' and 'Employer'


SAMPLE:
ROLE - - - - - EMPLOYER - - - COUNT
Global_User | A Company Ltd | 2
Super_User | A Company Ltd | 2


I have part of this working already (if you see above it counts for every type of role).
This is the code I have got so far:
SELECT     Role, Employer, COUNT(*) AS cnt
FROM dbo.SuperUs
GROUP BY Role, Employer
HAVING (COUNT(Role) > 1)

However, what I'm trying to do now is only count if the 'Role' is a 'Super_User' and ignore other roles like global user.
Post #1481805
Posted Wednesday, August 7, 2013 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
mrichardson 57577 (8/7/2013)
Can anyone help with this please?
I am trying to count all users in a table who are superusers (only if there is more than 1 super user).

TABLE: 'SuperUs'
FIELDS: 'Role' and 'Employer'


SAMPLE:
ROLE - - - - - EMPLOYER - - - COUNT
Global_User | A Company Ltd | 2
Super_User | A Company Ltd | 2


I have part of this working already (if you see above it counts for every type of role).
This is the code I have got so far:
SELECT     Role, Employer, COUNT(*) AS cnt
FROM dbo.SuperUs
GROUP BY Role, Employer
HAVING (COUNT(Role) > 1)

However, what I'm trying to do now is only count if the 'Role' is a 'Super_User' and ignore other roles like global user.


SELECT     Role, Employer, COUNT(*) AS cnt
FROM dbo.SuperUs
WHERE Role = 'Super_User'
GROUP BY Role, Employer
HAVING (COUNT(Role) > 1)




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1481809
Posted Wednesday, August 7, 2013 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
thanks, that's done the trick !
Post #1481810
Posted Wednesday, August 7, 2013 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
mrichardson 57577 (8/7/2013)
thanks, that's done the trick !


Great.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1481816
Posted Thursday, August 8, 2013 5:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
however... what seems to be harder to do is list data which has a count = 0 or <1.

e.g. we would also like to show which employers do not have a superuser.

But this isn't possible to show here because if there are no superusers in the 'group by' results - then they are not listed in the first place to count, so it always returns none.
Post #1482224
Posted Thursday, August 8, 2013 5:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
You will need to include both tables in your query:

FROM employers
LEFT JOIN superusers


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1482228
Posted Friday, August 9, 2013 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
I've managed to get thus far:
I've added my employer table and used a left join.

I can count all the users in total (sum of all users for that employer e.g. global users + superusers), but not just superusers.

using this code:
SELECT     e.EmployerName, { fn IFNULL(x.super_userCnt, 0) } AS super_userCnt
FROM dbo.mytableEmployer e LEFT OUTER JOIN
(SELECT employerName, COUNT(*) AS super_userCnt
FROM employerSuper_Users
GROUP BY employerName) x ON e.EmployerName = x.employerName

if I add a where clause - it either says invalid column name or incorrect syntax
Post #1482837
Posted Monday, August 12, 2013 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
SELECT 
e.EmployerName,
super_userCnt = ISNULL(x.super_userCnt, 0)
FROM dbo.mytableEmployer e
LEFT OUTER JOIN (
SELECT
employerName,
super_userCnt = COUNT(*)
FROM employerSuper_Users
WHERE [Role] = 'Super_User'
GROUP BY employerName
HAVING COUNT(*) > 1
) x ON e.EmployerName = x.employerName



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1483148
Posted Tuesday, August 13, 2013 3:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
Thanks a lot, that works!

(I just had a minor error on my code - I should have put if >0 and not 1)

also changed to COALESCE instead of ISNULL
Post #1483628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse