SQL Subquery issue - Invalid object name

Question

I have prepared the below "subquery" code. Error message: "Invalid object name 'group1-2'" is shown. May I know how to fix the issue.

select
  CASE
    WHEN reference_s = 'R1' THEN
      (select sum(DATEDIFF(second, datetime2_s,datetime6_s) ) from group1_2)
    WHEN reference_s = 'R2' THEN
      (select sum(DATEDIFF(second, datetime3_s,datetime6_s) ) from group1_2)
  END
  / (CAST(count(*) AS float)*60)
from
(
  select * from
  (
    select
      id,
      min(reference) as reference_s, min(datetime2) as datetime2_s,
      min(datetime3) as datetime3_s, max(datetime6) as datetime6_s,
      count(*) as type_s, sum(size) as size_s
    from dbo.testing
    group by id
  ) group1
  where type_s=2 and cntr_size_s=44
  UNION
  select * from
  (
    select
      id,
      min(reference) as reference_s, min(datetime2) as datetime2_s,
      min(datetime3) as datetime3_s, max(datetime6) as datetime6_s,
      count(*) as type_s, sum(size) as size_s
    from dbo.testing
    group by id
  ) group2
  where type_s=1
) group1_2
where datetime6_s IS NOT null
and datetime6_s >= '2023-03-01 00:00'
and datetime6_s <= '2023-03-30 23:59'

Answer 1

In your FROM clause you have a subquery the result of which you call group1_2. In your SELECT clause you have subqueries selecting from this. This is not allowed in SQL, because in the SELECT clause you look at a single row from the table (or an aggregated row of it). The SELECT clause does not know the whole table. If you want to define such an ad-hoc view that you can access multiple times in your query, you must make this a CTE (aka WITH clause).

But there are more flaws in your query:

• You have from (...) group1 where ... cntr_size_s = 44, but you don't select any cntr_size_s in group1.
• You have from (...) group2 where ... cntr_size_s = 44, but you don't select any cntr_size_s in group2.
• You have select case when reference_s = 'R1' ...end ... / cast(count(*) ...). So you access a column from group1_2 and an aggregation result. But there is no GROUP BY reference_s, which would give you an aggregation result per reference_s. So how could the data you are accessing here be aggregated to a single row (COUNT(*)) and not aggregated (reference_s) at the same time? This is not possible. GROUP BY reference_s could solve this syntactically, but I don't know whether this is the solution you are looking for.