Link to home
Start Free TrialLog in
Avatar of Brogrim
BrogrimFlag for Ireland

asked on

Subquery grouping

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))
Avatar of tim_cs
tim_cs
Flag of United States of America image

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)
Avatar of Brogrim

ASKER

that is returning 2 records on the group

the sub query returns 1953 records of which ether is 33 groups
ASKER CERTIFIED SOLUTION
Avatar of tim_cs
tim_cs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial