Susana Francisco Susana Francisco - 1 year ago 78
SQL Question

Column Invalid in the Selected List

So I have this scalar-function:

ALTER FUNCTION [dbo].[Custom_SumKayakoMonthProfit](@ClientId int, @KKId int, @StartDate SmallDateTime, @EndDate SmallDatetime, @Month int)

SELECT @Total = ((CAST(SUM(WorkedTimeInMinutes) AS FLOAT)/60) * KayakoValue)
FROM [VDBSERVER2012\SQLEXPRESS2014].PlaterITDB.dbo.KayakoTimeEntries
INNER JOIN ClientContract ON ClientId = @ClientId AND Workdate BETWEEN ClientContract.StartDt AND ClientContract.EndDt
WHERE OrganizationID = @KKId AND WorkDate BETWEEN @StartDate AND @EndDate AND MONTH(WorkDate) = @Month


Whenever I try to execute it to use it on a stored procedure, it gives me this error.

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

I can't figure out how to solve it.

Answer Source

What don't you understand about the message? You have an aggregation query with no GROUP BY. Hence, all columns must be the arguments to aggregation functions.

I suspect that you intend:

SELECT @Total = CAST(SUM(WorkedTimeInMinutes * KayakoValue) AS FLOAT)/60
FROM [VDBSERVER2012\SQLEXPRESS2014].PlaterITDB.dbo.KayakoTimeEntries kte INNER JOIN
     ClientContract cc
     ON ClientId = @ClientId AND
        Workdate BETWEEN cc.StartDt AND cc.EndDt
WHERE OrganizationID = @KKId AND
      WorkDate BETWEEN @StartDate AND @EndDate AND MONTH(WorkDate) = @Month

Note that you should learn to use table aliases and to qualify all column names. This is a good practice for writing readable and maintainable code.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download