Sql查询抛出错误 [英] Sql Query throwing error

问题描述

SELECT pmc.[month]                                    AS 'Month',
       pmc.pd_name_of_project                         AS 'Name of Project',
       tbl_div.name                                   AS 'Name of Advisory Services Division',
       TBL_PMC_UNIT.UNIT_NAME                         AS 'Name of Unit',
       pmc.staff_engineers,
       pmc.staff_clerical,
       pmc.staff_peons,
       pmc.pd_project_type                            AS 'Project Type',
       pmc.accepted_tender_cost                       AS 'Accepted Tender Cost',
       pmc.work_order_date                            AS 'Work Order Date',
       pmc.tender_period_months                       AS 'Tender Period',
       pmc.project_completion_date                    AS 'Project Completion Date',
       pmc.per_pmc_charges                            AS '% Of PMC Charges',
       pmc.total_pmc_charges_scheme                   AS 'Total PMC amount   of the Scheme',
       pmc.bill_amount_certified_upto_previous_month  AS 'Bill amount certified upto previous Month',
       pmc.total_PMC_charges_upto_previous_month      AS 'Total PMC charges  upto previous Month',
       pmc.receipt_PMC_charges_upto_previous_month    AS 'Receipt of PMC Charges upto previous Month',
       pmc.balance_of_PMC_charges_upto_previous_month AS 'Balance of PMC charges upto previous Month',
       pmc.bill_amount_certified_current_month        AS 'Bill amount certified During Current Month',
       pmc.PMC_charges_for_current_month              AS ' PMC charges  During Current Month',
       pmc.receipt_PMC_charges_current_month          AS 'Receipt of PMC Charges During Current Monthh',
       pmc.balance_of_PMC_charges_current_month       AS 'Balance of PMC charges During Current Month',
       SUM(pmc.salary_allowance)                      AS 'Salary Allowance'
FROM   TBL_PMC pmc
       INNER JOIN TBL_DIV
         ON TBL_DIV.ID = pmc.DIV_ID
       LEFT OUTER JOIN TBL_PMC_UNIT
         ON TBL_PMC_UNIT.ID = pmc.UNIT_ID
WHERE  pmc.div_id = 17
GROUP  BY pmc.[month]; 

这个查询给了我错误:-

This query is giving me the error :-

列TBL_PMC.pd_name_of_project"在选择列表中无效，因为它既没有包含在聚合函数中，也没有包含在 GROUP BY 子句中.

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

推荐答案

通过 GROUP 语句，您可以使用 SELECT 中包含在 GROUP BY 中或具有聚合函数的列.- 正如错误消息所说.

With the GROUP statement you can use columns in the SELECT which are contained in the GROUP BY or has an aggregate function. - as the error message said.

在这种情况下，您可以尝试使用 SUM...OVER (PARTITION BY ...) 子句.检查 MSDN.

You can try to use the SUM...OVER (PARTITION BY ...) clause in this case. Check MSDN.

因此删除 Group By 行并像这样更改您的总和:

So delete the Group By line and change your sum like this:

SUM(pmc.salary_allowance) OVER(PARTITION BY pmc.[month])

SUM(pmc.salary_allowance) OVER(PARTITION BY pmc.[month])

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