CODEWITHSUNDEEP
sqlsql-servert-sql
Shikha
Shikha

Reputation: 3

Sum functions doesn't show total values in single row after grouping

I have to showcase the total hours worked for each employee that worked on different dates.

I can use group by and sum function but I have multiple columns to showcase and it gives the same result sum with multiple rows. Additionally, I don't want to write every column name in group by clause as its not proper way of query writing.

I managed to get employees working hours from different tables using joins but couldn't sum up them in single row as a record. However it shows sum in multiple rows.

The script I used to get all the data is given below:

Select e.employeecode, Isnull(e.PreferredName,e.FirstNames) + '
    ' + e.LastName as [Name], w.Position
    , sum(h.TotalHours) over (partition by e.employeecode order by e.employeecode)[TotalHours]
    , e.Salary, e.startdate    
From employee e
Left join Appointment ap on ap.EmployeeCode = e.EmployeeCode
left join Work w on w.WorkCode = ap.workCode 
left join HistoricalAllowance h on h.EmployeeCode = e.EmployeeCode 
left join TransPerPaySequence TP on tp.PaySequence = h.PaySequence
Where tp.PeriodEnd between '2021-04-24' and '2021-05-07'
and h.AllowanceCode in ('99','1000') and
and ap.isactive ='1'
group by e.EmployeeCode

It looks right now after writing a script:

employeecode Name Position TotalHours Salary StartDate
1234 Anna employee-Com 63.45 500 40792
1234 Anna employee-Com 486.45 500 40792
2345 Jacky Manager 126.9 700 41395
2345 Jacky Manager 961.05 700 41395
2345 Jacky Manager 67.05 700 41395
3456 Mahato HR 402.3 570 41933
3456 Mahato HR 67.05 570 41933
3456 Mahato HR 126.9 570 41933
3456 Mahato HR 126.9 570 41933

The way I wanted

employeecode Name Position TotalHours Salary StartDate
1234 Anna employee-Com 549.9 500 40792
2345 Jacky Manager 1155 700 41395
3456 Mahato HR 723.15 570 41933

Please help and let me know what I am missing.

Thanks In advance

Upvotes: 0

Views: 841

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I can appreciate not wanting to list all the columns for the GROUP BY . . . but for a slightly different reason: If you can avoid the aggregation then the query is likely to be faster.

And SQL Server offers a convenient solution, using OUTER APPLY:

select e.employeecode,
       coalesce(e.PreferredName, e.FirstNames) + ' ' + e.LastName as Name,
       w.Position,
       h.TotalHoursTotalHours, e.Salary, e.startdate    
From employee e Left join
     Appointment ap
     on ap.EmployeeCode = e.EmployeeCode left join
     Work w
     on w.WorkCode = ap.workCode outer apply
     (select sum(h.TotalHours) as TotalHours
      from HistoricalAllowance h join 
           TransPerPaySequence TP
           on tp.PaySequence = h.PaySequence
      where h.EmployeeCode = e.EmployeeCode and
            tp.PeriodEnd between '2021-04-24' and '2021-05-07' and
            h.AllowanceCode in ('99','1000') 
     ) h
where ap.isactive = '1';

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

Remove the OVER(...) clause and then put every other (non SUMmed) column mentioned in the SELECT, into the GROUP BY also

Adhering to your it's not proper way of query writing rule is tripping you up on this one

ps, I've a doubt that the posted query in your question even runs - SQL server is not, to my knowledge, one of the few databases that will allow you to limit group by columns to just those on which all other selected, non aggregated columns are functionally dependent. Your query as posted should give a "column x in the select list is invalid because it is not an aggregate/in the group by" error

Upvotes: 1

Related Questions