SQL Summing Multiple Joins

I shortened the code quite a bit, but hopefully someone will get the idea of what i am tryign to do. Need to sum totals from two different selects, i tried putting each of them in Left Outer Joins(tried Inner Joins too). If i run wiht either Left Outer Join commented out, I get the correct data, but when i run them together, i get really screwed up counts. So, i know joins are probably not the correct approach to summing data from the same table, i can;t simple do it in a where clause there is other table involved int he code i commented out.

I guess i am trying to sum together 2 开发者_Go百科different queries.

SELECT  eeoc.EEOCode AS 'Test1',
        SUM(eeosum.Col_One) AS 'Col_One',

FROM EEO1Analysis eeo
    LEFT OUTER JOIN (
        SELECT eeor.AnalysisID, eeor.Test1,
                SUM(CASE eeor.ZZZ WHEN 1 THEN (CASE eeor.AAAA WHEN 1 THEN 1 ELSE 0 END) ELSE 0 END) AS 'Col_One',
        FROM EEO1Roster eeor
            ..........
        WHERE  eeor.AnalysisID = 7 
        GROUP BY eeor.AnalysisID, eeor.EEOCode
    ) AS eeosum2 ON eeosum2.AnalysisID = eeo.AnalysisID
    LEFT OUTER JOIN (
        SELECT eeor.AnalysisID, eeor.Test1,
                SUM(CASE eeor.ZZZ WHEN 1 THEN (CASE eeor.AAAA WHEN 1 THEN 1 ELSE 0 END) ELSE 0 END) AS 'Col_One',
        FROM EEO1Roster eeor
            ........
    ) AS eeosum ON eeosum.AnalysisID = eeo.AnalysisID 

WHERE  eeo.AnalysisID = 7
GROUP BY eeoc.Test1

You could UNION ALL the 2 queries and then do a SUM + GROUP BY i.e.

SELECT Col1, Col2, SUM(Col_One) FROM
     (SELECT Col1, Col2, SUM(Col_One) 
          FROM Table1 
          WHERE <Conditionset1> 
          GROUP BY Col1, Col2
     UNION ALL
     SELECT Col1, Col2, SUM(Col_One)
          FROM Table1 
          WHERE <Conditionset2> 
          GROUP BY Col1, Col2)
GROUP BY
     Col1, Col2

Of course, if there is are row(s) returned by and they would be double counted.

What about

SELECT ... FROM EEO1Analysis eeo,

  (SELECT ... LEFT OUTER JOIN ... GROUP BY ... ) AS data

...     

?

And, if you can, I'd recommend preparing the data to separate tables, then operate on them with different analysis IDs. Could save some execution time at least.

Need to sum totals from two different selects If you expect one row single-column result, this way is enough

SELECT 
   ((SELECT SUM(...) FROM ... GROUP BY...) + 
   (SELECT SUM(...) FROM ... GROUP BY...)) as TheSumOfTwoSums