question

mitchel avatar image
mitchel asked

SQL Server - Partition

Please help, any work around? Would like to sum up all the value of identical ticket numbers without grouping them and create new col(cost) and showing the total uniformly as shown below.

SELECT *, SUM(PRICE) OVER(PARTITION BY TICKET ORDER BY COUPON) AS COST

DATASOURCE:

TICKET ROUTE PAIRS COUPON PRICE

123456789 JFK-LAX-JFK LAX-JFK 2 500

123456789 JFK-LAX-JFK JFK-LAX 1 500

RESULT

TICKET ROUTE PAIRS COUPON PRICE

123456789 JFK-LAX-JFK JFK-LAX 1 500

123456789 JFK-LAX-JFK LAX-JFK 2 1000

DESIRED RESULT:

TICKET ROUTE PAIRS COUPON PRICE

123456789 JFK-LAX-JFK JFK-LAX 1 1000

123456789 JFK-LAX-JFK LAX-JFK 2 1000

sql server
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Define the boundaries for the window frame for the aggregate function SUM to be unbounded

SELECT *, SUM(PRICE) OVER(PARTITION BY TICKET ORDER BY COUPON 
          rows between unbounded preceding and unbounded following) AS COST

otherwise CURRENT ROW is the boundary ending point

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.