How to write a query to get details from two tables?

Question

0.00/5 (No votes)

See more:

I have two tables which are called "CustomerDetails" and "AmountMaster". Now I want to get "CustomerName, TotalAmount" from "CustomerDetails" table and I want to get total of "PaidAmount" from "AmountMaster" table.

My Query is like this :

SQL

select a.CustomerName, a.TotalAmount, SUM(b.PaidAmount )
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId where b.AmountTypeId=1 and b.PaymentToId=2
order by b.CustomerId

but here i'm getting error is like this

Msg 8120, Level 16, State 1, Line 1
Column 'CustomerDetails.CustomerName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here I want to get single record with total "PaidAmount". So can anybody help me please?

Posted 25-Aug-13 22:56pm

Aboobakkar Siddeq D U

Add a Solution

3 solutions

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)

Rakesh Meel · Answer 1 · 2013-08-25T23:01:00

Solution 1

for use of sum Function in query you have to use Group by

correct Query is belowing....

SQL

select a.CustomerName,  SUM(a.TotalAmount) as TotalAmount, SUM(b.PaidAmount ) as PaidAmount 
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId where b.AmountTypeId=1 and b.PaymentToId=2 group by a.CustomerName
order by b.CustomerId

Posted 25-Aug-13 23:01pm

Rakesh Meel

Comments

Aboobakkar Siddeq D U 26-Aug-13 5:31am

No here i'm getting same error, I don't want the sum of TotalAmount, I want only PaidAmount sum here. For Ex. CustomerId = 1 has many values in "AmountMaster" Table, so I don't want sum of TotalAmount, but i want sum of PaidAmount from "PaymentMaster" with same userid

Aarti Meswania · Answer 2 · 2013-08-25T23:03:00

Solution 2

you have use SUM() function so, you should agreegate columns given in select list using Group by clause

SQL

select a.CustomerName, a.TotalAmount, SUM(b.PaidAmount )
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId 
where b.AmountTypeId=1 and b.PaymentToId=2
Group by a.CustomerName, a.TotalAmount

Happy Coding!
:)

Posted 25-Aug-13 23:03pm

Aarti Meswania

Updated 25-Aug-13 23:52pm

v2

Comments

Aboobakkar Siddeq D U 26-Aug-13 5:31am

No here i'm getting same error, I don't want the sum of TotalAmount, I want only PaidAmount sum here. For Ex. CustomerId = 1 has many values in "AmountMaster" Table, so I don't want sum of TotalAmount, but i want sum of PaidAmount from "PaymentMaster" with same userid

Aarti Meswania 26-Aug-13 5:39am

check properly because I have wrote query that gives you sum of paidAmount only not total amount.
give an example input - output record so, it will easy to understand requirement.

Aboobakkar Siddeq D U 26-Aug-13 5:44am

Your query is gives error like this:

Msg 8127, Level 16, State 1, Line 5
Column "AmountMaster.CustomerId" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Aarti Meswania 26-Aug-13 5:52am

just remove order by clause

CodeBlack · Answer 3 · 2013-08-25T23:04:00

Solution 3

Update your query as mentioned below :

SQL

select a.CustomerName, a.TotalAmount, SUM(b.PaidAmount )
from CustomerDetails a join AmountMaster b on a.CustomerId=b.CustomerId where b.AmountTypeId=1 and b.PaymentToId=2 group by a.CustomerName, a.TotalAmount, b.PaidAmount 
order by b.CustomerId

Posted 25-Aug-13 23:04pm

CodeBlack

Comments

Aboobakkar Siddeq D U 26-Aug-13 5:31am

No here i'm getting same error, I don't want the sum of TotalAmount, I want only PaidAmount sum here. For Ex. CustomerId = 1 has many values in "AmountMaster" Table, so I don't want sum of TotalAmount, but i want sum of PaidAmount from "PaymentMaster" with same userid

CodeBlack 26-Aug-13 5:36am

can you post your data in form of temporary table with select query ?