两个不同的表在同一个数据库总和问题 [英] two different table in same database sum problem

问题描述

嗨



我正在使用c sharp和MSSQL 2008



我有两个表首先是条目和第二个是CustomerTable，表格有2个文本框，用于余额。





1'表：条目



日期CustomerCode客户名称类型Pu

2-10-13 CS001印度G 100

2-10-13 CS001 Indian S 200

2-10-13 CS001印度G 300

2-10-13 CS001印度S 400

2-10-13 CS001印度G 300



2'表：CustomerTable



CustomerCode客户名称PBalanceG PBalanceS



CS001印度200 400



我想要pu和Pbalance的总和，其中date = input CustomerCode = input and type = input



喜欢



2-10-13 cs001印度g = 100 + 300 + 300 + 200（PBalanceG）= 900

2-10-13 cs001 indian s = 200 + 400 + 400（PBalanceS）= 1000



答案是



txtTextBox1 = 900

txtTextBox2 = 1000



我将此查询用于一个值（g）



query = select from（txtPu），lblGBalance from Entry e INNER JOIN CustomerTable c ON e.txtCustomerCode = c.txtCustomerCode Group by e.txtCustomerCode;



它给出错误



列'CustomerTable.lblGBalance'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。



告诉我我的查询或任何其他价值（g，s）的解决方案有什么问题。

解决方案

嘿那里，



这是一个示例查询可以为您提供所需的金额，

  SELECT  
（选择 SUM（E1.PU） FROM 条目E1  WHERE  E1。[ TYPE ] = '  G'  AND  E1。[日期] = '  2-10-13'  AND  E1。 CustomerCode = '  CS001'）+ C.PBalanceG  AS  BalanceG，
（选择 SUM（E2.PU） FROM 条目E2  WHERE  E2。[ TYPE ] = '  S'  AND  E2。[日期] = < span class =code-string>'  2-10-13'  AND  E2.CustomerCode =  '  CS001'）+ C.PBalanceS  AS  BalanceS 
  FROM  CustomerTable C 
  WHERE  C.CustomerCode = '  CS001' 

如果有帮助请告诉我。



Azee ......

Quote：

列'CustomerTable.lblGBalance'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

根据您遇到的错误，您还应该包含 CustomerTable .lblGBalance 你的列分组依据条款。

我这样做了：

  SELECT  
（ SELECT  SUM（PU） FROM 条目 WHERE   TYPE  = '  G' ）+ A.PBalanceG  AS  BalanceG，
（ SELECT  SUM（PU）  FROM 条目 WHERE   TYPE  = '  S'）+ A.PBalanceS  AS  BalanceS 
  FROM  CustomerTable A 
  INNER  加入 ENTRY B  ON  A.CustomerCode = B.CustomerCode 
  WHERE  A.CustomerCode = '  CS001' 
  AND  B. 日期 = ' < span class =code-string> 2-10-13' 
  GROUP   BY  
 A.PBalanceG，A.PBalanceS 

希望它有所帮助！ ：）

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).

解决方案

Hey there,

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

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...

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:

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! :)

这篇关于两个不同的表在同一个数据库总和问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！