Link to home
Create AccountLog in
Avatar of adznon
adznon

asked on

MSSQL Count

Hello,

I am looking for the SQL code to count distinct the number of rows that AuditModuleCode = Exp
 
Data Example

Store_Number AuditModuleCode
1                         Exp
1                         Sup
2                         Exp
2                         Exp
2                         Sup
3                         Exp
3                         Sup

Example output wanted

Store_Number AuditModuleCode
1                         1
2                         2
3                         1
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of adznon
adznon

ASKER

Great, Thanks for the quick response worked perfectly
Another option could be -

/*------------------------

SELECT DISTINCT Store_Number, COUNT(*) OVER(PARTITION BY Store_Number) AuditModuleCode FROM tst
WHERE AuditModuleCode = 'Exp'
------------------------*/
Store_Number AuditModuleCode
------------ ---------------
1            1
2            2
3            1

(3 row(s) affected)

Open in new window

Welcome. Happy to help :)
Avatar of adznon

ASKER

Quick question how would you then add to only display if count is less than 8
We need to add Having Clause.

SELECT Store_Number, COUNT(*) AuditModuleCode FROM tst
WHERE AuditModuleCode = 'Exp'
GROUP BY Store_Number
HAVING COUNT(*) < 8
Avatar of adznon

ASKER

Perfect, cheers have a great day
Cheers.