MSSQL Count

adznon
adznon used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
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

Author

Commented:
Great, Thanks for the quick response worked perfectly
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Welcome. Happy to help :)

Author

Commented:
Quick question how would you then add to only display if count is less than 8
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
We need to add Having Clause.

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

Author

Commented:
Perfect, cheers have a great day
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Cheers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial