locked
sum between two values RRS feed

  • Question

  • User-47788187 posted

    Experts,

    How to get sum between these two values ?

    SELECT 
         
          [Soldtopt],
    	
    	  [tradingname],
    	  [DlvDate],
    	  SUM(try_cast(Netvalue as float)) as Netvalue,  
    	 count(distinct SDDoc)   as Salesdoc ,
    	 count(distinct case when  Netvalue = '0' then 1 else null end) as ZeroValue ,
    
    	 sum (count(distinct SDDoc)) , (count(distinct case when  Netvalue = '0' then 1 else null end)) As result
    
      FROM [FOC].[dbo].[foc]
    
    
    	GROUP by Soldtopt,tradingname,DlvDate  ORDER BY  count (distinct SDDoc) DESC; 
    

    is this correct to way to bring the sum(

    sum (count(distinct SDDoc)) , (count(distinct case when  Netvalue = '0' then 1 else null end)) As result)

    ? or i am getting error "

    Msg 130, Level 15, State 1, Line 13
    Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

    Tuesday, January 24, 2017 9:14 AM

Answers

  • User2117486576 posted

    If you are using Sql Server don't use SUM() just add the counts:

    count(distinct SDDoc) + count(distinct case when  Netvalue = '0' then 1 else null end) As result

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 24, 2017 1:41 PM

All replies

  • User2117486576 posted

    If you are using Sql Server don't use SUM() just add the counts:

    count(distinct SDDoc) + count(distinct case when  Netvalue = '0' then 1 else null end) As result

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 24, 2017 1:41 PM
  • User-47788187 posted

    Hello Richard,

    Your answer worked well. Also how to query the result = 0 only ? I mean with this query , i need to display only if the result(

    count(distinct SDDoc) + count(distinct case when  Netvalue = '0' then 1 else null end) As result)

    match eq to 0 value ?

    SELECT 
         
          [Soldtopt],
    	
    	  [tradingname],
    	  [DlvDate],
    	  SUM(try_cast(Netvalue as float)) as Netvalue,  
    	 count(distinct SDDoc)  as Salesdoc ,
    	 count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue ,
    
    	count(distinct SDDoc) - count(distinct case when  Netvalue = '0' then 1 else null end) As Result
    
      FROM [FOC].[dbo].[foc]
    
    
    	GROUP by Soldtopt,tradingname,DlvDate  ORDER BY  count (distinct SDDoc) DESC; 
    

    Tuesday, January 24, 2017 2:26 PM
  • User2117486576 posted

    Sorry, it is not clear to me what you want.  If you only want to return results when the ZeroValue and the Result in the select clause are equal then use a sub-query:

    select * from 
    (SELECT 
         
          [Soldtopt],
    	
    	  [tradingname],
    	  [DlvDate],
    	  SUM(try_cast(Netvalue as float)) as Netvalue,  
    	 count(distinct SDDoc)  as Salesdoc ,
    	 count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue ,
    
    	count(distinct SDDoc) - count(distinct case when  Netvalue = '0' then 1 else null end) As Result
    
      FROM [FOC].[dbo].[foc]) as x where x.ZeroValue = x.Result;

    If you only want to return results when ZeroValue is 0 then you can use a subquery:

    select * from 
    (SELECT 
         
          [Soldtopt],
    	
    	  [tradingname],
    	  [DlvDate],
    	  SUM(try_cast(Netvalue as float)) as Netvalue,  
    	 count(distinct SDDoc)  as Salesdoc ,
    	 count(distinct case when Netvalue = '0' then 1 else null end) as ZeroValue ,
    
    	count(distinct SDDoc) - count(distinct case when  Netvalue = '0' then 1 else null end) As Result
    
      FROM [FOC].[dbo].[foc]) x where x.ZeroValue = 0;

    If I have misunderstood your requirements, please provide more detailed information.

    Thursday, January 26, 2017 12:14 AM