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
adznonAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try this -

Explanation - You need to use GROUP BY Store_Number and then take the COUNT to get the number of AuditModuleCode values.

CREATE TABLE tst
(
	Store_Number INT 
	, AuditModuleCode VARCHAR(10)
)
GO

INSERT INTO tst VALUES
(1,'Exp'),
(1,'Sup'),
(2,'Exp'),
(2,'Exp'),
(2,'Sup'),
(3,'Exp'),
(3,'Sup')
GO

SELECT Store_Number, COUNT(*) AuditModuleCode FROM tst
WHERE AuditModuleCode = 'Exp'
GROUP BY Store_Number

Open in new window


OUTPUT

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

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

(3 row(s) affected)

Open in new window

0
 
adznonAuthor Commented:
Great, Thanks for the quick response worked perfectly
0
 
Pawan KumarDatabase ExpertCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Welcome. Happy to help :)
0
 
adznonAuthor Commented:
Quick question how would you then add to only display if count is less than 8
0
 
Pawan KumarDatabase ExpertCommented:
We need to add Having Clause.

SELECT Store_Number, COUNT(*) AuditModuleCode FROM tst
WHERE AuditModuleCode = 'Exp'
GROUP BY Store_Number
HAVING COUNT(*) < 8
0
 
adznonAuthor Commented:
Perfect, cheers have a great day
0
 
Pawan KumarDatabase ExpertCommented:
Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.