tsql query for counting a 'grouped by' and a where clause

Question

tsql query for counting a 'grouped by' and a where clause

mrichardson 57577

Right there with Babe

Points: 739
More actions
August 7, 2013 at 6:00 am

#296761

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

mrichardson 57577 Right there with Babe Points: 739 More actions · Answer 2

mrichardson 57577

Right there with Babe

Points: 739

August 7, 2013 at 6:11 am

#1639332

thanks, that's done the trick !

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

mrichardson 57577 (8/7/2013)
thanks, that's done the trick !

Great.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

mrichardson 57577 Right there with Babe Points: 739 More actions · Answer 4

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

mrichardson 57577 Right there with Babe Points: 739 More actions · Answer 6

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

mrichardson 57577 Right there with Babe Points: 739 More actions · Answer 8

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