Group by Name and sum column radnisati and prekovremeni sati

Goran Bibic

8y798SQL

Code from sql working fine

I need sum column radni sati and column prekovremeni sati

SUM (cast(radni_sati as float)) as Ukpuno_radni_sati

group by radnik...something like this...

SELECT a.IdPrijava, a.redni_broj , a.radnik, isnull(convert(varchar(20), a.vrijemeodjave, 113), '') as vrijemeodjave, convert(varchar(20), b.vrijemeprijave, 113) as vrijemeprijave,
CASE WHEN DATEPART(WEEKDAY, b.vrijemeprijave) in (1,7) then 0 else (CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END) end AS radni_sati,
CASE WHEN DATEPART(WEEKDAY, a.vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 then ((DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600)-8) ELSE NULL END) end AS prekovremeni
FROM(SELECT IdPrijava, redni_broj, radnik, vrijemeodjave, ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum
FROM dbo.prijava_radnika) a INNER JOIN(SELECT IdPrijava, redni_broj, radnik, vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika) b ON a.IdPrijava = b.IdPrijava where 1 = 1

Group by Name and sum column radnisati and prekovremeni sati

5 Replies

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

Nitin Sontakke8y ago
Accepted answer
You CANNOT have normal row data AND sum in the same result set. You will need to fire another query. Just imagine how will sql server return the resultset.

If you are going to use same query as above, create a view and use the view for the purpose of SUM.
+1
Goran Bibic8y ago
Solved
SELECT  [radnik] as Radnik,
SUM (cast((CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) then 0 else (CASE WHEN (DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END) end)as float))   AS 'Radni sati',
SUM (cast((CASE WHEN DATEPART(WEEKDAY, vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8  then ((DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600)-8) ELSE NULL END) end)as float))   AS 'Prekovremeni'
from [prijava_radnika]
group by [radnik]
0
Goran Bibic8y ago
I have some result
SELECT  radnik, --isnull(convert(varchar(20), vrijemeodjave, 113), ''), convert(varchar(20), vrijemeprijave, 113)
CASE WHEN (DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END   AS radni_sati,
CASE WHEN DATEPART(WEEKDAY, vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8  then ((DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600)-8) ELSE NULL END) end AS prekovremeni
--SUM (cast(radni_sati as float)) as ukupno
from prijava_radnika
--group by radnik
Its ok...I just need sum
0
Goran Bibic8y ago
Like this...but error is line 4,5 and 8

Msg 8120, Level 16, State 1, Line 13
Column 'a.vrijemeodjave' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'radni_sati'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'prekovremeni'.
SELECT a.radnik, isnull(convert(varchar(20), a.vrijemeodjave, 113), '') as vrijemeodjave, convert(varchar(20), b.vrijemeprijave, 113) as vrijemeprijave,
CASE WHEN DATEPART(WEEKDAY, b.vrijemeprijave) in (1,7) then 0 else (CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END) end    AS radni_sati,
CASE WHEN DATEPART(WEEKDAY, a.vrijemeodjave) in (1,7) then (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8  then ((DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600)-8) ELSE NULL END) end AS prekovremeni,
SUM (cast(radni_sati as float)) as ukupno1,
SUM (cast(prekovremeni as float)) as ukupno2
FROM(SELECT IdPrijava, redni_broj, radnik, vrijemeodjave, ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum
FROM dbo.prijava_radnika) a INNER JOIN(SELECT IdPrijava, redni_broj, radnik, vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika) b ON a.IdPrijava = b.IdPrijava where 1 = 1
group by a.radnik
0
Goran Bibic8y ago
Regards my friend...

I try this...and work...just need to put repair this code...

This is for monthly sum work hours and overtime hours

I try this for 1 worker and work
select radnik, SUM (cast(radni_sati as float)) as kolicina from prijava_radnika where radnik = 'Gojko Bibic' group by radnik
0