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."

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;

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.

Member

9 Points

72 Posts

## sum between two values

Jan 24, 2017 09:14 AM|av2020|LINK

Experts,

How to get sum between these two values ?

is this correct to way to bring the sum(

? 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."

Contributor

6864 Points

1980 Posts

## Re: sum between two values

Jan 24, 2017 01:41 PM|RichardY|LINK

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

Member

9 Points

72 Posts

## Re: sum between two values

Jan 24, 2017 02:26 PM|av2020|LINK

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(

match eq to 0 value ?

Contributor

6864 Points

1980 Posts

## Re: sum between two values

Jan 26, 2017 12:14 AM|RichardY|LINK

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:

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

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