i cant group a column in sql server

siva nathan

9y791Learn iOS Programming

my table record is below in screen shot

my query is belw

declare @FromDate datetime

set @FromDate='03-01-17'

declare @Todate datetime

set @Todate='03-19-17'

select

sum(round(OThrs,0)) as OThrs, convert(int,round((( ME.AuditSalary+HRA)/8),0)* convert(int,round(isnull(OThrs,0),0)*2)) as OTSalary

from master_salary as MS

inner join Master_Employee as ME on ME.id=MS.Refid

Where convert(varchar(10),MS.Attdate,110) between @FromDate and @Todate and ME.Category in(2)

group by ME.AuditSalary ,HRA,OThrs

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
I already told you to use EXPRESSION used in select statement in the group by clause. I will ONLY give example here..so you change accordingly.

select ltrim(rtrim(round(column, 2))) [somecolumn], sum(column1) [somesum]
from tablename
group by ltrim(rtrim(round(column, 2)))

Hope you get the point.
0
siva nathan9y ago
attdate empid othours(varchar column)
03-01-17 1 2
03-02-17 1 2.05
while rounding othours column im getting 4 thats fine
while calculating like this
declare @FromDate datetime
set @FromDate='03-01-17'
declare @Todate datetime
set @Todate='03-19-17'
select
sum(round(OThrs,0)) as OThrs,
convert(int,round((( ME.AuditSalary+HRA)/8),0)* convert(int,sum(round(isnull(OThrs,0),0)))) as OTSalary,

from master_salary as MS
inner join Master_Employee as ME on ME.id=MS.Refid
Where convert(varchar(10),MS.Attdate,110) between @FromDate and @Todate and ME.Category in(2)
group by ME.AuditSalary ,HRA,OThrs
im getting
othours otsalary
2 30
2.05 30
my expected result is
othours otsalary
4 60

0
Nitin Sontakke9y ago
My dear friend siva, please read replies very carefully and please provide information on ALL the points i mentioned.

Can you provide data in text format, just few records?

Can you please tell what you are getting?

AND

Can you please tell what you are expecting?

Please be descriptive. Type more. Tell more. Inform more. Share more.
0
siva nathan9y ago
this is my query ,,AuditSalary=120 and also im including my table
declare @FromDate datetime
set @FromDate='03-01-17'
declare @Todate datetime
set @Todate='03-19-17'
select
sum(round(OThrs,0)) as OThrs

from master_salary as MS
inner join Master_Employee as ME on ME.id=MS.Refid
Where convert(varchar(10),MS.Attdate,110) between @FromDate and @Todate and ME.Category in(2)
group by ME.AuditSalary ,HRA,OThrs

0
siva nathan9y ago
i have a table in that table i have many records of employee present date,time,othours,etc
i want to calculate othours of emoloyee for a month (i.e)03-01-17 to03-31-17
in othours column values like 2,2.05,2.0546 etc while grouping these columns wont work correctly,and also i want to calculate othour salary (ie) (salary /8)*othours ,
my error result is
othours otsalary
2 30
2.05 30
0
Nitin Sontakke9y ago
You cannot use derived (alias) column names in group by. Use the expression used in select statement itself.

Please express your question clearly. What error are you getting. Please provide data in text format (preferably the script) so that we can put in a temp table and provide you the working query.

What is the expected output? You will need to mention that as well.
0

attdate	empid	othours(varchar column)
03-01-17	1	2
03-02-17	1	2.05