Aggregate function error in sqlserver

Posted by Klbaiju under Sql Server on 2/13/2014 | Points: 10 | Views : 2031 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,following code is working one

.SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime
FROM [Baiju].[dbo].[HbaEmp]
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

I want to add a case when statement in the code.final code is as follows

SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,dstatus=
(CASE WHEN CONVERT(TIME,intime,108)>'08:35' and CONVERT(TIME,intime,108) <'11:00' THEN 'L' WHEN CONVERT(TIME,intime,108)>'11:00' THEN 'halfday' else 'right' end )
FROM [Baiju].[dbo].[HbaEmp]
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
following error is showing

Msg 8120, Level 16, State 1, Line 3
Column 'Baiju.dbo.HbaEmp.InTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'Baiju.dbo.HbaEmp.InTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'Baiju.dbo.HbaEmp.InTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
how to solve this

Regards

Baiju

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Kshaik on: 2/14/2014 [Member] Starter | Points: 25

0	SELECT EmpId , CAST(InTime AS DATE) as workingday , EmpName, RIGHT('0' +CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)], convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime , dstatus= (CASE WHEN CONVERT(TIME,min(intime),108)>'08:35' and CONVERT(TIME,min(intime),108) <'11:00' THEN 'L' WHEN CONVERT(TIME,min(intime),108)>'11:00' THEN 'halfday' ELSE 'right' END ) FROM [HbaEmp] GROUP BY EmpId , EmpName, CAST(InTime AS DATE) Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

SELECT EmpId ,
CAST(InTime AS DATE) as workingday ,
EmpName,
RIGHT('0' +CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2)
+ ':' +
RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime
,

dstatus=
(CASE
WHEN CONVERT(TIME,min(intime),108)>'08:35' and CONVERT(TIME,min(intime),108) <'11:00' THEN 'L' WHEN CONVERT(TIME,min(intime),108)>'11:00' THEN 'halfday'
ELSE 'right'
END
)

FROM [HbaEmp]

GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kshaik on: 2/14/2014 [Member] Starter | Points: 25

0	executed successfully Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 25

0	Hi, you cant specify the columns other than in group by in select statement.ofcoursee you can keep using aggregate function. but in this case you cant do what you want using group by the following query may help you or mat give you your desired result SELECT * ,dstatus= (CASE WHEN CONVERT(TIME,intime,108)>'08:35' and CONVERT(TIME,intime,108) <'11:00' THEN 'L' WHEN CONVERT(TIME,intime,108)>'11:00' THEN 'halfday' else 'right' end ) FROM [Baiju].[dbo].[HbaEmp] Hope this helps you revert if you have any issues sravan Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
you cant specify the columns other than in group by in select statement.ofcoursee you can keep using aggregate function.
but in this case you cant do what you want using group by

the following query may help you or mat give you your desired result

SELECT * 
,dstatus=
(CASE
 WHEN CONVERT(TIME,intime,108)>'08:35' and CONVERT(TIME,intime,108) <'11:00'
  THEN 'L' 
  WHEN CONVERT(TIME,intime,108)>'11:00'
   THEN 'halfday' 
   else 
   'right' 
   end )
FROM  [Baiju].[dbo].[HbaEmp]

Hope this helps you
revert if you have any issues

sravan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 25

0	Hi, Please ignore my previous post This would give you correct result SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)], convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,dstatus= (CASE WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00' THEN 'L' WHEN CONVERT(TIME,MIN(InTime),108)>'11:00' THEN 'halfday' else 'right' end ) FROM Temp_EmpTimes GROUP BY EmpId , EmpName, CAST(InTime AS DATE) Mark as answer if satisfied sravan Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Please ignore my previous post
This would give you correct result


SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,dstatus=
(CASE
 WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00' 
 THEN 'L'
  WHEN CONVERT(TIME,MIN(InTime),108)>'11:00' 
  THEN 'halfday' 
  else 'right' end )
FROM Temp_EmpTimes
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

Mark as answer if satisfied

sravan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 2/14/2014 [Member] Starter | Points: 25

0	hi sravan your code is working fine. I want to solve another problem. RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2)-convert(varchar(5), max(OutTime)-MIN(InTime),108) means i want subtract total work_hours-Emp_Intime. how it is possible Regards Baiju Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

hi sravan your code is working fine.
I want to solve another problem.

RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2)-convert(varchar(5), max(OutTime)-MIN(InTime),108)
means i want subtract
total work_hours-Emp_Intime.
how it is possible
Regards
Baiju

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 25

0	Mark as answer if WORKED sravan Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts