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
Microsoft SQL Server
Last Comment
Pawan Kumar
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
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.