locked
sum function over partition by order by RRS feed

  • Question

  • Hi , 

    I am trying to use sum function over partition by using order by for cummulative summing. but i am getting syntax error.below is my code:

     SELECT
          *,
          SUM(disp_fields_pop) OVER(
            PARTITION BY measure_id,
            mem_nbr,
            num_col_name,
            baseeventepisode,
            case when measure_id = 61 then isnull(d_meas_indic_desc, meas_indic_desc) else null end
          )
        ORDER BY
          DISPLAY_RANK
        ) AS display_cnt
        FROM
          temp_table

    Please help !

    Thursday, August 6, 2020 9:50 AM

All replies

  • There is an opening brace missing after ORDER BY

     SELECT
          *,
          SUM(disp_fields_pop) OVER(
            PARTITION BY measure_id,
            mem_nbr,
            num_col_name,
            baseeventepisode,
            case when measure_id = 61 then isnull(d_meas_indic_desc, meas_indic_desc) else null end
          )
        ORDER BY (
          DISPLAY_RANK
        ) AS display_cnt
        FROM
          temp_table


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 6, 2020 10:17 AM
  • Sorry My Bad ! , the code should be like this :

    SELECT
          *,
          SUM(disp_fields_pop)

    OVER(
            PARTITION BY measure_id,
            mem_nbr,
            num_col_name,
            baseeventepisode,
            case when measure_id = 61 then isnull(d_meas_indic_desc, meas_indic_desc) else null end      
        ORDER BY DISPLAY_RANK)
         AS display_cnt

        FROM
          temp_table

    P.S. I am still getting syntax error as:
    Incorrect syntax near 'order'.

    Thursday, August 6, 2020 12:01 PM
  • WITH CTE AS (
    SELECT
    	*,
    	CASE WHEN measure_id = 61 THEN ISNULL(d_meas_indic_desc, meas_indic_desc) ELSE NULL END AS temp_meas_indic_desc
    FROM
    	temp_table
    )
    
    SELECT 
    	*,
    	SUM(disp_fields_pop) OVER(
    		PARTITION BY 
    			measure_id,
    			mem_nbr,
    			num_col_name,
    			baseeventepisode,
    			temp_meas_indic_desc
    		ORDER BY DISPLAY_RANK) AS display_cnt
    FROM
    	temp_table;


    A Fan of SSIS, SSRS and SSAS

    • Proposed as answer by Naomi N Thursday, August 6, 2020 2:26 PM
    Thursday, August 6, 2020 2:16 PM
    Answerer
  • You need to remove the ) right before ORDER BY to get the query working without errors. Can you post some input and desired output?

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 6, 2020 2:24 PM
  • Hi, 

    yes, the query posted on question is syntatically wrong . i have posted the query in my next comment after that.Anyways here it is ,

    SELECT
          SUM(disp_fields_pop) 
      OVER
      (
            PARTITION BY measure_id,
            mem_nbr,
            num_col_name,
            baseeventepisode,
            case when measure_id = 61 then isnull(d_meas_indic_desc, meas_indic_desc) else null end     
            ORDER BY DISPLAY_RANK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
         AS display_cnt
        FROM
          temp_table;

    I have 2008R2 Sql Server running on my machine. It seems the query will work on version 12 or higher. Am i correct on this or is there anything i can do about this in 2008??

    Thursday, August 6, 2020 2:43 PM
  • Yes, ORDER BY for SUM was added in SQL Server 2012. Are you sure you need to still support SQL Server 2008 version?

    You would not be able to run this version of the query to get running totals.

    What is the input and desired output?


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 6, 2020 2:50 PM
  • Hi Gunner,

    Since there are no proper input and desired output, please refer below example and modify your query if you will still use SQL Server 2008R2.

    WITH CTE AS (
    SELECT
    	*,
    	CASE WHEN measure_id = 61 THEN ISNULL(d_meas_indic_desc, meas_indic_desc) ELSE NULL END AS temp_meas_indic_desc
    FROM
    	temp_table
    )
    
    select SUM(b.disp_fields_pop) 
    from  cte a 
    cross join cte b
    where (b.measure_id<=a.measure_id)
    group by a.measure_id,
    			a.mem_nbr,
    			a.num_col_name,
    			a.baseeventepisode,
    			a.temp_meas_indic_desc
    		ORDER BY a.DISPLAY_RANK

    In additon, you could also try with more methods like CURSOR and recursive common table expression.

    Please refer more examples from below:

    Calculate a Running Total in SQL Server

    Calculate running total / running balance

    Best Wishes

    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 7, 2020 3:00 AM