Sql question

SQL Table Coloums
GroupNumber
StoreNumber
AuditResponceMet
AuditStatus

Sample Data
GroupNumber / StoreNumber / AuditResponceMet / AuditStatus
1 / 1234 / N / Open
2 / 4567 / Y / Open
1 / 1234 / N Pending Approval
1 / 2544 / N / Pending Approval

Need to pull back all based on the group number

Distinct GroupNumber / Count Distinct Stores in group / Count Store Questions / Count AuditResponceMet = 'N' / Count AuditStatus = 'Pending Aproval'

Expected responce form sample data

1 / 2 / 3 / 3 / 2
2 / 1 / 1 / 0 / 0
adznonAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try this solution.

DATA GENERATION
CREATE TABLE Groups
(
	 GroupNumber INT 
	,StoreNumber INT
	,AuditResponceMet CHAR(1)
	,AuditStatus VARCHAR(100)
)
GO

INSERT INTO Groups VALUES
(1,1234,'N','Open'),
(2,4567,'Y','Open'),
(1,1234,'N','Pending Approval'),
(1,2544,'N','Pending Approval')
GO

Open in new window

SOLUTION
SELECT GroupNumber,COUNT(DISTINCT StoreNumber) StoreNumbers, COUNT(AuditResponceMet) AuditResponceMet,
SUM(CASE WHEN AuditResponceMet = 'N' THEN 1 ELSE 0 END) AuditResponceMetN,
SUM(CASE WHEN AuditStatus = 'Pending Approval' THEN 1 ELSE 0 END) AuditResponceMetN
FROM Groups
GROUP BY GroupNumber

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
GroupNumber StoreNumbers AuditResponceMet AuditResponceMetN AuditResponceMetN
----------- ------------ ---------------- ----------------- -----------------
1           2            3                3                 2
2           1            1                0                 0

(2 row(s) affected)

Open in new window

0
 
adznonAuthor Commented:
Perfect, Many thanks
0
 
Pawan KumarDatabase ExpertCommented:
welcome. Glad to help as always. :)
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.