Answered by:
sum between two values
Question

User47788187 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 
User47788187 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 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]) 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