Apply subquery result to every row in outer query

Question

I have written a subquery like so:

(
  SELECT SUM(X.kMax) 
        FROM (
            SELECT MAX(Val) AS kMax 
            FROM [Consumers] upc 
            GROUP BY upc.[Profile]
        ) X
)

It gets the maximum value for each profile and then sums up everything to return one value. E.g 99

How would I go about applying this value to every single row in the outer query?

Table

ID   Maxed
1      99
2      99
3      99
4      99
5      99

Answer 1

You need to write your query like followng.

select 
<columns>,
(
  SELECT SUM(X.kMax) 
        FROM (
            SELECT MAX(Val) AS kMax 
            FROM [Consumers] upc 
            WHERE UPC.ID=UT.ID
            GROUP BY upc.[Profile]
        ) X
)
from yourTable ut

Answer 2

You can apply a value using CROSS APPLY or OUTER APPLY

select 
  <columns>,
  X.kMax
from yourTable T
CROSS APPLY (
  SELECT SUM(X.kMax) AS kMax 
        FROM (
            SELECT MAX(Val) AS kMax 
            FROM [Consumers] upc 
            WHERE UPC.ID=UT.ID
            GROUP BY upc.[Profile]
        ) X
) X