I am trying to write a compound state that queries the results of a UNION in MS SQL 2005. I am trying the following structure but can't get it to work:
The result of the union looks like
name1 | 200
name2 | 100
name1 | 300
name2 | 150
I want to run a query on the result of the union so I end up with
name1 | 500
name2 | 250
select PrimaryAuthor, SUM(ND1) FROM
(select PrimaryAuthor, COUNT(PrimaryAuthor) as ND1
Where PrimaryAuthor is not null and PrimaryAuthor <> 'Null'
GROUP BY PrimaryAuthor
select SecondaryAuthor, COUNT(SecondaryAuthor) as ND2
Where PrimaryAuthor is not null and SecondaryAuthor <> 'Null'
GROUP BY SecondaryAuthor)