Brainripples

SELECT
-- various aggregation functions
FROM RecipientGroup g
CROSS JOIN 
  (SELECT 0 AS b_UsingTestData UNION SELECT 1) AS TestDataFlag
LEFT OUTER JOIN
  RecipientData rd ON  rd.GroupID=g.GroupID 
                   AND rd.b_IsTestRecord = TestDataFlag.b_UsingTestData
GROUP BY
  g.GroupID, TestDataFlag.b_UsingTestData
ORDER BY
  g.GroupID, TestDataFlag.b_UsingTestData

OK, SQLheads, is there a better way to ensure that every group's aggregate data includes rows for both the test records and the live records, even if a particular group only has test or live data? Because that cross join in bold feels like it's either incredibly elegant and idiomatic or the ugliest kludgy hack ever, and I'm too close to it to be able to tell which. (This is for Microsoft SQL Server 2000 and 2005)