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
CodePudding user response:
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
CodePudding user response:
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