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

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.

回答1:

The reason you're getting that error is that when you do a SUM() function you must group by any of the columns that are being returned.

回答2:

Since pmc.[month] is the only cloumn in your query listed in the group by clause, it's the only column name which may appear without beeing used with an aggregate function in your column list. It's hard to tell what you're trying to do with your query, by the looks of it, grouping might not be the way to go on this one.

回答3:

you have to use aggregate functions like MIN(),MAX(),AVG() for all the columns in the select statement except pmc.[month] column as it is used in the group by operation

your query should be something like this:

select pmc.[month] as 'Month',
max(pmc.pd_name_of_project) as 'Name of Project',
max(tbl_div.name) AS 'Name of Advisory Services Division',
max(TBL_PMC_UNIT.UNIT_NAME) AS 'Name of Unit',
.........
.........
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];

回答4:

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.

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

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

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