We help IT Professionals succeed at work.

How to write this SQL?

techques
techques asked
on
Medium Priority
222 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

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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)

Author

Commented:
select accountid from account in 2nd solution is incorrect. as there is no such field.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.