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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
Welcome. Happy to help :)
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
SELECT Store_Number, COUNT(*) AuditModuleCode FROM tst
WHERE AuditModuleCode = 'Exp'
GROUP BY Store_Number
HAVING COUNT(*) < 8
ASKER
Perfect, cheers have a great day
Cheers.
ASKER