sql server - Trying to use UNION on multiple queries but won't work because of my subquery uses AVG

Question

I'm trying to basically run 6 select queries(displayed two for the sake of readability but its basically that same pattern) and using union to create a single output. However, when I run the query, I get 2 of the following errors.

• Msg.141 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operation.
• Msg.10734 Variable assignment is not allowed in a statement containing a top-level UNION, INTERSECT or EXCEPT operator.

I understand and have looked at similar questions but nothing seems to work.

I would like to have the output look like this

|Tabe|Rent|Thd|
---------------------------------
|table1   | 9999      | 8888    |
|table2   | 9999      | 8888    |

Any suggestions or direction would be greatly appreciated!

question from:https://stackoverflow.com/questions/65891456/trying-to-use-union-on-multiple-queries-but-wont-work-because-of-my-subquery-us

Answer 1

I don't see why you need variables at all here. You need an OVER clause to calculate the average over all the rows:

Select
    'table1' as TableName,
    count(*) as RecordCount,
    0.75 * AVG(count(*)) over ()
from table1
where @something = specificDate
group by specificDate

union all

Select
    'table2',
    count(*) as RecordCount,
    0.75 * AVG(count(*)) over ()
from table2
where @something = specificDate
group by specificDate;

I note that your query appears to be filtering on specificdate. SO you can just group by the empty set: group by ()