Home SQL Server: SUM() with Row_number() - The sum off all records with row #1
Reply: 0

SQL Server: SUM() with Row_number() - The sum off all records with row #1

user2153
1#
user2153 Published in May 28, 2018, 3:17 am

I am trying to sum all row_number = 1 but I'd like it to include all other rows but only display the record for row_number 1 but sum all the other rows.

SELECT 
    A.TEST, A.BLOCK, A.POLICY, A.SAL_COVERAGE,
    A.YEAR, A.ZIP, A.COVERAGE,
    SUM(A.WP) AS WP,
    SUM(A.WE) AS WE,
    SUM(A.EP) AS EP,
    SUM(A.EE) AS EE
FROM
    (SELECT
         ROW_NUMBER() OVER(PARTITION BY ZIP ORDER BY ZIP) AS TEST,
         BLOCK, POLICY, SAL_COVERAGE,
         YEAR, ZIP, COVERAGE,
         SUM(WP) AS WP,
         SUM(WE) AS WE,
         SUM(EP) AS EP,
         SUM(EE) AS EE
     FROM 
         [Reports].[dbo].[DGTESTING]
     GROUP BY
         BLOCK, POLICY, SAL_COVERAGE, YEAR, ZIP, COVERAGE) AS A
WHERE 
    TEST = 1
    AND A.POLICY = 'XXXX'
GROUP BY
    A.TEST, A.BLOCK, A.POLICY, A.SAL_COVERAGE, A.YEAR, A.ZIP, A.COVERAGE

The result I am getting is like this:

I would expect every ZIP to be summed into this record but I am only getting 1. It looks like SQL is doing everything correct but I am missing a way to do what I am intending.

Sample data

--------------------------------------------------------------------------------------
|TEST|BLK|POL |SAL|YEAR|ZIP   |COV  |WP  |WE        |EP    |EE
--------------------------------------------------------------------------------------
|1   |1  |XXX |1  |2014|96003 |Bod  |-143|-10.888157|-87.59|-6.67
--------------------------------------------------------------------------------------
|32  |6  |XXX |1  |2015|96007 |Bod  |0   |0         |-55.41|-4.21
--------------------------------------------------------------------------------------

Expected data

-------------------------------------------------------------------------------------
|TEST|BLK|POL|SAL|YEAR|ZIP  |COV|WP  |WE        |EP  |EE
--------------------------------------------------------------------------------------
|1   |  1|XXX|  1|2014|96003|Bod|-143|-10.888157|-143|-10.88
------------------------------------------------------------------------------------

Here is more data

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.322547 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO