Bad joinery: error when selecting from joins

Question

I am attempting to create an hourly dispatch report so for the "hourly" part (in SQL Server 2016) i tried to make queries for each hour and join them but i am getting errors that i cannot understand the query is like so:

Select
*
From
(SELECT
  PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar) as '9 to 10'
from
  [order] o
  LEFT OUTER JOIN Order_LifeHistory h ON o.pkOrderID = h.fkOrderId
  LEFT OUTER JOIN PackageGroups pg ON o.fkPackagingGroupId = pg.PackageCategoryID
Where
  datepart(hour, dprocessedon) between 9
  and 10
group by
  PackageCategory
) as firstset
FULL JOIN (
SELECT
  PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar) as '9 to 10'
from
  [order] o
  LEFT OUTER JOIN Order_LifeHistory h ON o.pkOrderID = h.fkOrderId
  LEFT OUTER JOIN PackageGroups pg ON o.fkPackagingGroupId = pg.PackageCategoryID
Where
  datepart(hour, dprocessedon) between 10
  and 11
group by
  PackageCategory
)as secondset on firstset.PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar) = secondset.PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar)

and I keep getting the following errors:

Invalid column name 'PackageCategory'.
Invalid column name 'UpdatedBy'.
Invalid column name 'PackageCategory'.
Invalid column name 'UpdatedBy'.

I cannot understand this as each query that i am joining works on its own.
am i missing something obvious?
please excuse my work a i'm very new at this and tend towards the Frankenstein method of writing queries (Digging up other peoples bits and pieces attach them together and hope for a metaphorical bolt of lightning) thank you for reading

Answer 1

Because your firstset and secondset does not contain these columns. They both contain only a single column called 9 to 10.

your full join's on clause should be firstset.[9 to 10] = secondset.[9 to 10]

Select
*
From
(SELECT
  PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar) as '9 to 10'
from
  [order] o
  LEFT OUTER JOIN Order_LifeHistory h ON o.pkOrderID = h.fkOrderId
  LEFT OUTER JOIN PackageGroups pg ON o.fkPackagingGroupId = pg.PackageCategoryID
Where
  datepart(hour, dprocessedon) between 9
  and 10
group by
  PackageCategory
) as firstset
FULL JOIN (
SELECT
  PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar) as '9 to 10'
from
  [order] o
  LEFT OUTER JOIN Order_LifeHistory h ON o.pkOrderID = h.fkOrderId
  LEFT OUTER JOIN PackageGroups pg ON o.fkPackagingGroupId = pg.PackageCategoryID
Where
  datepart(hour, dprocessedon) between 10
  and 11
group by
  PackageCategory
)as secondset on firstset.[9 to 10] = secondset.[9 to 10]

Answer 2

You're not selecting PackageCategory, you're selecting it's value plus other strings as a column named 9 to 10. Read it again thinking about it:

SELECT
  PackageCategory + ' Count ' + Cast (Count (UpdatedBy) as varchar) as '9 to 10'

There are no columns named PackageCategory or UpdatedBy in your select.