Sql Query Throwing Error

SELECT pmc.[month] AS 'Month', pmc.pd_name_of_project AS 'Name of Project', tbl_div.name

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

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

Solution 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 
INNERJOIN  TBL_DIV 
ON          TBL_DIV.ID = pmc.DIV_ID
LEFTOUTERJOIN TBL_PMC_UNIT 
ON          TBL_PMC_UNIT.ID=pmc.UNIT_ID  
WHERE       pmc.div_id=17GROUPby    pmc.[month];

Solution 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])