Solved

How to write this SQL?

Posted on 2009-06-29
3
169 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 208R2 not recognizing DBF file in linked Server 11 65
configure service broker on all databases 2 88
How to query LOCK_ESCALATION 4 42
Grid querry results 41 80
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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