CODEWITHSUNDEEP
sqlsql-serverdatabasedatetimegroup-by
Nabeel Yousaf
Nabeel Yousaf

Reputation: 21

DateTime group by hour in SQL

I want to group by on Hour and in select column "time" I want to show full time format (Example: 12:15:56 PM) but when I run may code the following error is showing.

Error: Column 'vFlowRate_hr.Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select Sum(Value) As FlowRateSum, Dir.PlantAddress, PlantType, FlowRateNo,vFlowRate_hr.Date , time 
from vFlowRate_hr Inner Join DIR ON vFlowRate_hr.FlowRateID = DIR.ID
where FlowRateID = @FlowRateID and DIR.PlantType = @PlantType
and vFlowRate_hr.Date BETWEEN @FromDate AND @ToDate
group by FlowRateNo,Dir.PlantAddress,PlantType,vFlowRate_hr.Date, DATEPART(Hour, time)

Upvotes: 1

Views: 1313

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Presumably, you want the hour:

select Sum(Value) As FlowRateSum, Dir.PlantAddress, PlantType, FlowRateNo, vFlowRate_hr.Date,
       datepart(hour, time)
 from vFlowRate_hr Inner Join
      DIR
      ON vFlowRate_hr.FlowRateID = DIR.ID
 where FlowRateID = @FlowRateID and DIR.PlantType = @PlantType and
       vFlowRate_hr.Date BETWEEN @FromDate AND @ToDate
 group by Dir.PlantAddress, PlantType, FlowRateNo, vFlowRate_hr.Date,
       datepart(hour, time)

Then the select will match the group by.

Note that I changed the group by so the columns are in the same order as in the select -- easier to validate.

Also, you should qualify all column references, so it is clear where they come from. As is, the query will still run but it is less maintainable and updatable with the potentially ambiguous column references.

Upvotes: 2

Related Questions