Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

The following SQL throws the error
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How can I fix it?


select (select sum((select countervalue from alltransaction where alltranid=t.id)) from alltransaction sub_t 
inner join client c on t.clientid = c.id inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) and sub_t.currency <= t.currency 
and sub_t.transactiontypeid <= t.transactiontypeid and sub_t.id <= t.id) as Balance from alltransaction t
inner join client c on t.clientid = c.id 
inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) order by [transactiontypeid] asc

Open in new window

techquesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mohed SharfiConnect With a Mentor Vice CEOCommented:
Hi,
yes I Mean you test only sub query and see the result, it must be return one value.
thanks
0
 
Mohed SharfiVice CEOCommented:
Hi techques,
How are you, please try to use TOP 1
thanks
select top 1(select sum((select countervalue from alltransaction where alltranid=t.id)) from alltransaction sub_t 
inner join client c on t.clientid = c.id inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) and sub_t.currency <= t.currency 
and sub_t.transactiontypeid <= t.transactiontypeid and sub_t.id <= t.id) as Balance from alltransaction t
inner join client c on t.clientid = c.id 
inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) order by [transactiontypeid] asc

Open in new window

0
 
Mohed SharfiVice CEOCommented:
Hi ,
you also try select top 100 percent if you need to result all data
thanks
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
techquesAuthor Commented:
Still the same error

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

0
 
Mohed SharfiVice CEOCommented:
Ok Try this
SELECT TOP 100 PERCENT (SELECT ( SELECT SUM(countervalue) from alltransaction where alltranid=t.id) from alltransaction sub_t  
inner join client c on t.clientid = c.id inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) and sub_t.currency <= t.currency 
and sub_t.transactiontypeid <= t.transactiontypeid and sub_t.id <= t.id) as Balance from alltransaction t
inner join client c on t.clientid = c.id 
inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) order by [transactiontypeid] asc

Open in new window

0
 
techquesAuthor Commented:
A new error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 
Mohed SharfiVice CEOCommented:
Hi,
this with referencing the object name in sub query, please try it & feed back
thanks
SELECT TOP 100 PERCENT (SELECT ( SELECT SUM(alltransaction.countervalue) from alltransaction where alltransaction.alltranid=t.id) from alltransaction sub_t  
inner join client c on t.clientid = c.id inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) and sub_t.currency <= t.currency 
and sub_t.transactiontypeid <= t.transactiontypeid and sub_t.id <= t.id) as Balance from alltransaction t
inner join client c on t.clientid = c.id 
inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id where alltranid = 0 and t.clientid = '153' and trandatetime >= '2009-07-14' 
and trandatetime < '2009-07-15' and transactiontypeid in (1,2,3,4) order by [transactiontypeid] asc

Open in new window

0
 
techquesAuthor Commented:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 
Mohed SharfiVice CEOCommented:
Hi ,
try to use group by in sub query to get on row result, that depend on your data.
thanks
0
 
techquesAuthor Commented:
I am sorry I do you understand what do you mean 'use group by in sub query to get on row result' as it is just a sum of a select query and do not has group by.

0
All Courses

From novice to tech pro — start learning today.