Subquery grouping

Brogrim
Brogrim used Ask the Experts™
on
I am attempting to group data from a sub query

The 1st part is to filter teh records works fine

SELECT     YEAR(dbo.tblMemberROC.CommensementofService) AS Expr1, dbo.tblMemberROC.ROCSUStatusID AS Expr2
FROM         dbo.tblMemberROC INNER JOIN
                      dbo.tblMember ON dbo.tblMemberROC.MemberID = dbo.tblMember.MemberID
WHERE     (dbo.tblMemberROC.ROCSUStatusID = 3 OR
                      dbo.tblMemberROC.ROCSUStatusID = 4))

I now want to group the records using this sql


SELECT     YEAR(dbo.tblMemberROC.CommensementofService) AS Expr3, COUNT(dbo.tblMemberROC.ROCSUStatusID) AS Expr4
GROUP BY YEAR(dbo.tblMemberROC.CommensementofService)

Where (
SELECT     YEAR(dbo.tblMemberROC.CommensementofService) AS Expr1, dbo.tblMemberROC.ROCSUStatusID AS Expr2
FROM         dbo.tblMemberROC INNER JOIN
                      dbo.tblMember ON dbo.tblMemberROC.MemberID = dbo.tblMember.MemberID
WHERE     (dbo.tblMemberROC.ROCSUStatusID = 3 OR
                      dbo.tblMemberROC.ROCSUStatusID = 4))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
SELECT     YEAR(a.Expr1) AS Expr3, COUNT(a.Expr2) AS Expr4
FROM (
SELECT     YEAR(dbo.tblMemberROC.CommensementofService) AS Expr1, dbo.tblMemberROC.ROCSUStatusID AS Expr2
FROM         dbo.tblMemberROC INNER JOIN
                      dbo.tblMember ON dbo.tblMemberROC.MemberID = dbo.tblMember.MemberID
WHERE     (dbo.tblMemberROC.ROCSUStatusID = 3 OR
                      dbo.tblMemberROC.ROCSUStatusID = 4)) a
GROUP BY YEAR(a.Expr1)
BrogrimInformation Systems Development Manager

Author

Commented:
that is returning 2 records on the group

the sub query returns 1953 records of which ether is 33 groups
Commented:
Please provide data examples of what you expect to get and what you are getting.  

I also just noticed this could be simplified a little more.

SELECT     a.Expr1 AS Expr3, COUNT(a.Expr2) AS Expr4
FROM (
SELECT     YEAR(dbo.tblMemberROC.CommensementofService) AS Expr1, dbo.tblMemberROC.ROCSUStatusID AS Expr2
FROM         dbo.tblMemberROC INNER JOIN
                      dbo.tblMember ON dbo.tblMemberROC.MemberID = dbo.tblMember.MemberID
WHERE     (dbo.tblMemberROC.ROCSUStatusID = 3 OR
                      dbo.tblMemberROC.ROCSUStatusID = 4)) a
GROUP BY a.Expr1

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