Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

How to write this SQL?

Hi

I need to get the sum of amount field in a table which has the id in accoutgroupid in another table.

But, the following query returns error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


select sum(amount) from alltransaction where currency = 'USD' and accountid 
in (select * from account where currency = 'USD' and accountgroupid = 0 and id > 0)

Open in new window

0
techques
Asked:
techques
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do
select sum(amount) from alltransaction 
where currency = 'USD' 
 and accountid  in (select id from account where currency = 'USD' and accountgroupid = 0 and id > 0) 
or this: 

select sum(t.amount) 
from alltransaction t
where t.currency = 'USD' 
 and exists( select null from account a where a.currency = 'USD' and a.accountgroupid = 0 and a.id > 0 and a.id = t.accountid ) 

or even simpler: 
 
select sum(t.amount) 
from account a
join alltransaction  t
  on t.accountid = a.id
 and t.currency = a.currency
where a.currency = 'USD' 
  and a.accountgroupid = 0 
  and a.id > 0

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this helps:

select sum(amount) from alltransaction
where currency = 'USD' and accountid
in (select accountid from account where currency = 'USD' and accountgroupid = 0 and id > 0)
0
 
techquesAuthor Commented:
select accountid from account in 2nd solution is incorrect. as there is no such field.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now