Solved

How to write this SQL?

Posted on 2009-06-29
3
168 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
3 Comments
 
LVL 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now