sql-servert-sqlscalar-subquery

Apply subquery result to every row in outer query

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
Solution

  • 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