SQL code questions

Archived Topics
1

I have a query with the following code:

SELECT TOP (100) PERCENT machine, SUM(CASE WHEN lunch = 1 THEN (runtime) END) AS lunch_time, SUM(CASE WHEN paperwrk = 1 THEN (runtime) END) AS paper_work, SUM(CASE WHEN stpbar = 0 AND lunch = 0 AND paperwrk = 0 THEN (runtime) END) AS idle_time, SUM(runtime) AS totaltime, shift, jobtask FROM dbo.Prodtrack GROUP BY machine, shift, jobtask ORDER BY machine

What I am trying to add in is the T_STAMP from Prodtrack table. But when I add it in it wants to make it part of the GROUP BY statement as well and it messes up the output from the query. I have tried not having in the GROUP BY but when I do this, it comes up with the error about not being aggrigate or in group by. I have tried without success MIN and MAX but they dont seem to pick up the correct information. Is there a command I am not familiar with that would allow me to do as I require?

All I want it to do is add in the value from the T_STAMP column at the end of the data provided by the GROUP BY statement. Hopefully this is clear.

Thanks and have a great day.

2

Well, when you’re grouping, each unique group becomes 1 row in the output. So, you have to perform some aggregate expression on the t_stamp, otherwise it wouldn’t know which value of t_stamp from the group’s rows to use. What t_stamp do you want it to use? You could use MIN(T_STAMP) for the first timestamp in the group, or MAX(T_STAMP) for the last…