内联接错误的Sql组请帮帮我 [英] Sql group by error in inner join please help me

问题描述

我有3个表我内部加入3个表并添加功能但现在收到错误

请帮我解决问题请帮帮我





错误：

i have 3 table i inner join with 3 table and add function but now getting error
please help me how to solve the the problem please help me


ERROR :

引用：

消息8120，级别16，状态1，行1

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

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

表1：TBL_pur_inv为pr

purinvid slno pdcgst cgstamt pdsgst sgstamt pdigst igstamt TaxableAmt totaltaxamt



表2：tbl_party_ldg AS p



partyID prtynm prtgst prtpan



表3：TBL_pur_invdet AS prd

purinvid purinvdt invno invdt partyIDpur invamt



我尝试过：

TABLE 1: TBL_pur_inv as pr
purinvid slno pdcgst cgstamt pdsgst sgstamt pdigst igstamt TaxableAmt totaltaxamt

TABLE 2: tbl_party_ldg AS p

partyID prtynm prtgst prtpan

TABLE 3: TBL_pur_invdet AS prd
purinvid purinvdt invno invdt partyIDpur invamt

What I have tried:

select p.prtgst,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst,sum(prd.totaltaxamt)As Rate,sum(prd.TaxableAmt)As Taxable_Amount
from TBL_pur_inv as pr
 INNER JOIN tbl_party_ldg  AS p   ON pr.partyIDpur = p.partyID
 INNER JOIN TBL_pur_invdet  AS prd   ON pr.purinvid = prd.purinvid
 
where purinvdt ='2018-01-04' GROUP BY  prd.pdcgst

推荐答案

错误信息非常明确......您应该将 GROUP BY 部分更改为：

The error message is quite clear... You should change a GROUP BY part to:

GROUP BY p.prtgst,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst

SQL ag gregate函数（如sum）有一些规则 - 理由......

你不能创建一个行（列表列表），其中同一个raw包含简单值和聚合值...除外您的简单值用于在查询中创建组（中断）...



简而言之 - SQL查询将为每个项返回一行，但是聚合函数为数据中的所有行返回单个值...

SQL aggregate functions (like sum) have some rules - of reason...
You can not create a row (a list of columns) where the same raw contains both simple and aggregated values... except if your simple values are used to create groups (breaks) in the query...

The reason in nutshell - An SQL query will return a single row for each item, but aggregate function returns a single value for all the rows in the data...

这篇关于内联接错误的Sql组请帮帮我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！