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

Question

I am getting the above error for the query below :

SELECT 
    a.stid, a.pfid, pf.pfname, a.mdid, 
    mm.mdDescription, a.categoryid, a.SummedSpace,
    a.AvgSpace, mm.size_sqft as MML_size, a.TotalRev,
    a.TotalProfit, a.wkCount, a.Lastwkinst 
FROM
   (SELECT wkid, stid, mdid, pfid, categoryid,
           sum([space]) as SummedSpace, avg([space]) as AvgSpace,
           sum(revenue) as TotalRev, sum(profit) as TotalProfit,
           count(wkid) as wkCount, max(wkid) as Lastwkinst
    FROM dbo.mdSpaceSales
    WHERE wkid > 200936 AND categoryid in (25, 16, 62, 4079)
    GROUP BY stid, mdid, pfid, categoryid) AS a
LEFT JOIN mdmasterlist mm ON mm.mdid = a.mdid
LEFT JOIN 
    (SELECT distinct pfid, [pf name] as pfname FROM mdmasterlist) AS pf
       ON pf.pfid = a.pfid
ORDER BY 
    stid, categoryid, a.pfid, a.mdid

Could anyone help me with this error?

Answer 1

In the subquery you say:

SELECT wkid,stid,mdid,pfid,categoryid,SUM()... 
group by stid,mdid,pfid,categoryid

I think it needs to be:

SELECT wkid,stid,mdid,pfid,categoryid,SUM()... 
group by wkid,stid,mdid,pfid,categoryid

Else you are trying to make SQL Server guess which wkid you want in the select list. Maybe your intention is to not break out each wkid (since you do not seem to expose it in the outer query), in which case it should be:

SELECT stid,mdid,pfid,categoryid,SUM()... 
group by stid,mdid,pfid,categoryid

Answer 2

The inner derived query: you have to either group on (collapse) or aggregate wkid

You are trying to do both:

SELECT .. wkid .. count(wkid) .. max(wkid) .. GROUP BY stid, mdid, pfid, categoryid

I suspect you need to remove the first wkid thus:

...
FROM
   (SELECT /*wkid drop me*/, stid, mdid, pfid, categoryid,
           sum([space]) as SummedSpace, avg([space]) as AvgSpace,
           sum(revenue) as TotalRev, sum(profit) as TotalProfit,
           count(wkid) as wkCount, max(wkid) as Lastwkinst
    FROM dbo.mdSpaceSales
    WHERE wkid > 200936 AND categoryid in (25, 16, 62, 4079)
    GROUP BY stid, mdid, pfid, categoryid) AS a
LEFT JOIN
...

A simple rule for aggregates: you have to be able to express your query in plain language.