two different table in same database sum problem

Question

0.00/5 (No votes)

See more:

Hi

I am using c sharp and MSSQL 2008

I have two tables first is Entry and second is CustomerTable and form have a 2 textboxes for sum of balance.

1'st table: Entry

Date CustomerCode CustomerName Type Pu
2-10-13 CS001 Indian G 100
2-10-13 CS001 Indian S 200
2-10-13 CS001 Indian G 300
2-10-13 CS001 Indian S 400
2-10-13 CS001 Indian G 300

2'nd Table: CustomerTable

CustomerCode CustomerName PBalanceG PBalanceS

CS001 Indian 200 400

I want to sum of pu and Pbalance where date= input CustomerCode=input and type=input

like

2-10-13 cs001 indian g=100+300+300+200(PBalanceG)=900
2-10-13 cs001 indian s=200+400+400(PBalanceS)=1000

Ans is

txtTextBox1=900
txtTextBox2=1000

I am using this query for one value (g)

query = "select sum(txtPu), lblGBalance from Entry e INNER JOIN CustomerTable c ON e.txtCustomerCode=c.txtCustomerCode Group By e.txtCustomerCode";

it gives a error

Column 'CustomerTable.lblGBalance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tell me what is wrong in my query or any other solution for both of value (g,s).

Posted 10-Oct-13 20:45pm

indiancodinglove

Add a Solution

2 solutions

Solution 2

Quote:
Column 'CustomerTable.lblGBalance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

According to your error encountered, you should also include the CustomerTable.lblGBalance column in you Group By Clause.
I've done this:

SQL

SELECT
    (SELECT SUM(PU) FROM Entry WHERE TYPE = 'G') + A.PBalanceG AS BalanceG  ,
    (SELECT SUM(PU) FROM Entry WHERE TYPE = 'S') + A.PBalanceS AS BalanceS
FROM CustomerTable A
INNER JOIN ENTRY B ON A.CustomerCode = B.CustomerCode
WHERE A.CustomerCode = 'CS001'
    AND B.Date = '2-10-13'
GROUP BY
 A.PBalanceG, A.PBalanceS

Hope it helps! :)

Posted 10-Oct-13 22:21pm

berrymaria

Updated 10-Oct-13 22:55pm

v2

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)

**Azee** · Accepted Answer · 2013-10-10T21:47:00

Hey there,

This is a sample query can get you the sums that you need,

SQL

SELECT 
(Select SUM(E1.PU) FROM Entry E1 WHERE E1.[TYPE] = 'G' AND E1.[Date] = '2-10-13' AND E1.CustomerCode='CS001') + C.PBalanceG AS BalanceG,
(Select SUM(E2.PU) FROM Entry E2 WHERE E2.[TYPE] = 'S' AND E2.[Date] = '2-10-13' AND E2.CustomerCode='CS001') + C.PBalanceS AS BalanceS
FROM CustomerTable C
WHERE C.CustomerCode = 'CS001'

Let me know if it helps.

Azee...