CODEWITHSUNDEEP
sqlsql-server
Shawn
Shawn

Reputation: 5260

SQL Window aggregate function to hide repeating values

I got this query using window aggregate function in SQL Server.

SELECT customer,location, invoice_number, qty,
       Sum(qty) OVER ( PARTITION BY customer,location) AS sub_total  
  FROM myTable

The result shows as

  customer  location  invoice_number  qty  sub_total
    479249  441        800002309    -8.00  -20.00
    479249  441        800002310    -4.00  -20.00
    479249  441        800002311    -8.00  -20.00 
    481439  441        800003344    -1.00   -1.00 
    483553  441        800003001    -8.00  -19.50
    483553  441        800003001    -8.00  -19.50
    483553  441        800003001    -3.50  -19.50

But I would like it to hide the repeating subtotal as 

  customer  location  invoice_number  qty  sub_total
    479249  441        800002309    -8.00  
    479249  441        800002310    -4.00  
    479249  441        800002311    -8.00  -20.00 
    481439  441        800003344    -1.00   -1.00 
    483553  441        800003001    -8.00  
    483553  441        800003001    -8.00  
    483553  441        800003001    -3.50  -19.50

How can I do that?

Upvotes: 2

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can do that with a complex case statement:

SELECT customer, location, invoice_number, qty,
       (case when row_number() over (partition by customer, location order by invoice_number desc) = 1 
             then Sum(qty) over (partition by customer, location)end) AS sub_total
FROM myTable 
ORDER BY customer, location, invoice_number;

The final ORDER BY is important. SQL results sets represent unordered sets without an ORDER BY. The data may look like it is in the right order, but that is not necessarily true unless explicitly declared as such.

Upvotes: 6

Related Questions