Solved

How to write this SQL?

Posted on 2009-06-29
3
172 Views
Last Modified: 2012-05-07
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
Comment
Question by:techques
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24734636
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24734641
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
 

Author Closing Comment

by:techques
ID: 31597842
select accountid from account in 2nd solution is incorrect. as there is no such field.

0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question