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: Today @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Today @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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