Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to write this SQL?

Posted on 2009-06-29
3
Medium Priority
?
175 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 150 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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