Menu
I am trying to accomplish the following:
SELECT
*,
CASE WHEN 1 THEN SUM(b.myField) ELSE AVG(b.myField) END OVER (PARTITION BY ID)
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID
Is this possible with the window functions in SQL Server?
I am able to accomplish the following without the case statement:
SELECT
*,
SUM(b.myField) OVER (PARTITION BY ID)
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID
775
We can use the CASE Expression in any statement or clause that allows a valid expression. For example, you can use CASE Expression in statements such as SELECT , UPDATE , DELETE , SET and in clauses such as PARTITION BY , ORDER BY , WHERE and HAVING .
A window function computes a value for each row in the window. PARTITION BY expr_list PARTITION BY is an optional clause that subdivides the data into partitions. Including the partition clause divides the query result set into partitions, and the window function is applied to each partition separately.
Basic windowing syntax The first part of the above aggregation, SUM(duration_seconds) , looks a lot like any other aggregation. Adding OVER designates it as a window function. You could read the above aggregation as "take the sum of duration_seconds over the entire result set, in order by start_time ."
You can try this:
SELECT
*,
CASE WHEN (SUM(b.myField)
OVER (PARTITION BY ID))=1 THEN SUM(b.myField)
ELSE AVG(b.myField) END
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
