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

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

David Gomez
1#
David Gomez Published in 2018-02-14 03:10:18Z

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

Vladimir Baranov
2#
Vladimir Baranov Reply to 2018-02-15 04:28:17Z

The query below would return one row for the whole table as in your expected result in the question. It will return one row with the minimum ZIP value.

SUM(...) OVER() sums the whole table, for each row. Then we are picking just one row with rn=1. Run the inner query first to understand what it does.

SELECT 
    A.TEST
    ,A.BLOCK
    ,A.POLICY
    ,A.SAL_COVERAGE
    ,A.YEAR
    ,A.ZIP
    ,A.COVERAGE
    ,A.WP
    ,A.WE
    ,A.EP
    ,A.EE
FROM
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY ZIP) AS rn
            ,BLOCK
            ,POLICY
            ,SAL_COVERAGE
            ,YEAR
            ,ZIP
            ,COVERAGE
            ,SUM(WP) OVER() AS WP
            ,SUM(WE) OVER() AS WE
            ,SUM(EP) OVER() AS EP
            ,SUM(EE) OVER() AS EE
        FROM [Reports].[dbo].[DGTESTING]
    ) AS A
WHERE 
    rn = 1
;
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO