A typical query needed

Question

3.00/5 (1 vote)

See more:

Hi everybody,

I have a tables as

AccountID | Date | Amount |
-----------------------------------
1 | 2012-11-07 | 5000 |
2 | 2012-11-07 | 6000 |
3 | 2012-11-07 | 1000 |
4 | 2012-11-14 | 5000 |
and another mapping table as

AccountID | PersonID |
-----------------------
1 | 1 |
2 | 1 |
3 | 2 |
4 | 1 |

Now I want to get the total of "Amount" which belongs to "PersonID" = 1 and the date should be "2012-11-07"...

Please reply ASAP...
Thanks in advance

With regards,
Srivikas.

Posted 7-Nov-12 2:48am

Srivikas

Add a Solution

3 solutions

Solution 3

SQL

select sum(Amount),table2.personid from table1
Join table2 ON table.AccountID = table2.AccountID 
where table2.PersonID =1 
and table1.Date  = "2012-11-07"
group by table2.personid

Posted 7-Nov-12 3:46am

kankeyan

Comments

Srivikas 9-Nov-12 1:02am

Thank you very much.. :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Accepted Answer · 2012-11-07T03:24:00

Solution 2

Try:

SQL

select sum(Amount) from table1, table2 where table.AccountID = table2.AccountID and
table2.PersonID =1 and table1.Date  = "2012-11-07"

Posted 7-Nov-12 3:24am

Shanalal Kasim

Comments

Srivikas 9-Nov-12 1:01am

Thank you very much.. :)

Shanalal Kasim 9-Nov-12 1:07am

welcome

jim lahey · Accepted Answer · 2012-11-07T02:55:00

Solution 1

In untested SQL I give you:

SQL

Select Sum(Amount) as Amount from Account inner join PersonAccount on Amount.AccountID=PersonAccount.AccountID where PersonAccount.AccountID=1 and Account.Date='2012-11-07'

This assumes the names of the tables which weren't supplied in the question.

I hope it works.

Posted 7-Nov-12 2:55am

jim lahey

Comments

Srivikas 9-Nov-12 1:02am

Thank you very much.. :)