SUM COLUMN1(radni_sati) and COLUMN2(prekovremeni) ORDER by (radnik) SQL?

Question

1.00/5 (2 votes)

See more:

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...

Some help?

I need result (example) column sum for worker (radnik)

Nr radnik

radni_sati

prekovremeni

1

Gojko Bibic

69 21

2

Stevo Stevic

122 41

3+...etc

What I have tried:

1469	1	Gojko Bibic	15 Mar 2018 16:55:45	15 Mar 2018 06:55:43	8	2
1470	2	Stevo Stevic	16 Mar 2018 18:14:21	16 Mar 2018 06:58:33	8	3
1471	3	Stevo Stevic	16 Mar 2018 17:46:34	16 Mar 2018 08:17:20	8	1
1472	4	Gojko Bibic	16 Mar 2018 15:58:18	16 Mar 2018 06:58:17	8	1
1473	5	Test bbb	16 Mar 2018 13:58:20	16 Mar 2018 06:58:19	7	NULL
1474	6	Radnik_test	16 Mar 2018 15:58:22	16 Mar 2018 08:58:21	7	NULL
1475	7	gfsdghsdfhdgh	16 Mar 2018 19:58:24	16 Mar 2018 10:58:23	8	1
1477	8	fjhjff	16 Mar 2018 14:13:41	16 Mar 2018 11:10:28	3	NULL
1478	9	Stevo Stevic	16 Mar 2018 16:55:45	16 Mar 2018 06:12:54	8	2
1479	10	Stevo Stevic	16 Mar 2018 16:55:45	16 Mar 2018 15:12:54	1	NULL
1482	11	Bibic Goran2	16 Mar 2018 16:55:45	16 Mar 2018 07:35:57	8	1
1483	12	Gojko Bibic	16 Mar 2018 16:55:45	16 Mar 2018 11:54:08	5	NULL
1484	13	Gojko Bibic	15 Mar 2018 16:55:45	15 Mar 2018 06:55:43	8	2
1485	14	Goran Panic	15 Mar 2018 16:55:45	15 Mar 2018 11:57:24	4	NULL
1486	16	Goran Panic	15 Mar 2018 16:55:45	15 Mar 2018 07:07:24	8	1
1487	15	Mitar Miric	16 Mar 2018 18:55:45	16 Mar 2018 08:57:24	8	1
1488	17	Stevo Stevic	16 Mar 2018 17:23:20	16 Mar 2018 08:22:57	8	1
1489	18	Stevo Stevic	16 Mar 2018 17:23:20	16 Mar 2018 07:23:18	8	2
2669	19		04 Apr 2018 12:24:11	04 Apr 2018 12:00:44	0	NULL
2671	20	Gojko Bibic	04 Apr 2018 12:24:15	04 Apr 2018 12:24:14	0	NULL
2672	21	Gojko Bibic	04 Apr 2018 12:24:17	04 Apr 2018 12:24:16	0	NULL
2673	22	Gojko Bibic	04 Apr 2018 12:24:22	04 Apr 2018 12:24:18	0	NULL
2674	23	Gojko Bibic	04 Apr 2018 12:24:24	04 Apr 2018 12:24:23	0	NULL
2675	24	Gojko Bibic	04 Apr 2018 12:24:30	04 Apr 2018 12:24:28	0	NULL
2678	25		04 Apr 2018 12:32:11	04 Apr 2018 12:31:13	0	NULL
2679	26	Gojko Bibic	04 Apr 2018 12:33:52	04 Apr 2018 12:31:23	0	NULL
2680	29		04 Apr 2018 12:34:40	04 Apr 2018 12:33:44	0	NULL
2681	28	Gojko Bibic	04 Apr 2018 12:34:16	04 Apr 2018 12:33:55	0	NULL
2682	27	Stevo Stevic	04 Apr 2018 12:34:00	04 Apr 2018 12:33:58	0	NULL
2683	30	Stevo Stevic	04 Apr 2018 12:34:50	04 Apr 2018 12:34:08	0	NULL
2684	32	Gojko Bibic	04 Apr 2018 12:42:52	04 Apr 2018 12:34:18	0	NULL
2685	31		04 Apr 2018 12:42:49	04 Apr 2018 12:34:47	0	NULL
2687	33	Gojko Bibic	04 Apr 2018 12:42:54	04 Apr 2018 12:42:53	0	NULL
2689	34	Stevo Stevic	04 Apr 2018 12:43:03	04 Apr 2018 12:43:02	0	NULL
2691	35	Gojko Bibic	04 Apr 2018 12:43:38	04 Apr 2018 12:43:35	0	NULL
2693	36	Gojko Bibic	04 Apr 2018 12:45:16	04 Apr 2018 12:45:15	0	NULL
2694	37	Stevo Stevic	04 Apr 2018 12:45:54	04 Apr 2018 12:45:21	0	NULL
2695	38	Test bbb	04 Apr 2018 12:45:56	04 Apr 2018 12:45:41	0	NULL
2696	39	Radnik_test	04 Apr 2018 12:46:16	04 Apr 2018 12:46:09	0	NULL
2697	40	sfdgsdfgsfg	04 Apr 2018 13:55:25	04 Apr 2018 13:56:14	0	NULL
2698	41	Stevo Stevic	05 Apr 2018 10:17:41	05 Apr 2018 10:11:38	0	NULL
2699	42	Gojko Bibic	05 Apr 2018 10:24:12	05 Apr 2018 10:15:13	0	NULL
2700	43	Gojko Bibic	05 Apr 2018 11:30:46	05 Apr 2018 10:25:18	1	NULL
2701	45	Gojko Bibic	01 Apr 2018 14:40:04	01 Apr 2018 07:38:41	0	7
2702	46	Stevo Stevic	06 Apr 2018 14:40:05	06 Apr 2018 11:38:43	3	NULL
2703	44	Test bbb	06 Apr 2018 14:28:17	06 Apr 2018 11:38:56	2	NULL
2704	50	Test RF Citac radnik	01 Apr 2018 20:52:01	01 Apr 2018 06:27:02	0	14
2705	48	Bibic Goran2	06 Apr 2018 20:51:32	06 Apr 2018 14:27:22	6	NULL
2706	49	Test1	31 Mar 2018 20:51:47	31 Mar 2018 14:40:07	0	6
2707	47	Goran Panic	06 Apr 2018 20:51:31	06 Apr 2018 14:40:07	6	NULL
2708	51	Test RF Citac radnik	09 Apr 2018 14:39:49	09 Apr 2018 06:09:04	8	NULL
2709	52	Bibic Goran2		09 Apr 2018 18:09:40	NULL	NULL
3710	53	Test RF Citac radnik	29 Apr 2018 13:18:52	29 Apr 2018 13:02:03	0	0

Posted 29-Apr-18 22:01pm

Goran Bibic

Updated 29-Apr-18 23:35pm

Add a Solution

2 solutions

Solution 2

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]

Solved...thank you all people...

Posted 29-Apr-18 23:36pm

Goran Bibic

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-04-29T22:42:00

Solution 1

It's difficult to work out what you are trying to do here - your explanation is very unclear - but try this:

SQL

SELECT Worker, SUM(radni_sati), SUM (prekovremeni_sati) 
FROM MyTable
GROUP BY Worker

If that isn't what you are trying to do, you need to explain much more clearly exactly what you are trying to achieve, perhaps with column names, sample input and sample output data.

Posted 29-Apr-18 22:42pm

OriginalGriff

Comments

Goran Bibic 30-Apr-18 4:44am

That work...but...radni_sati and prekovremeni_sati are dinamicly created column..
No have value in sql table

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

Goran Bibic 30-Apr-18 4:45am

I need to modify this code...but I dont have idea becose are that two column are created from resuls

OriginalGriff 30-Apr-18 4:49am

JOIN that table to your original , and use the GROUP BY on that.

Goran Bibic 30-Apr-18 4:59am

No, that is not solution becose datetime column dont have sum when doing job

Goran Bibic 30-Apr-18 5:04am

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

I need here to transform to have just 3 column, instead 5
I not need column vrijemeprijave and vrijemeodjave in table...but need for counting column radni_sati and column prekovremeni

Need result
radnik radni_sati prekovremeni
1 Gojko Bibic 8 1
2 Stevo Stevic 6 2
3 Gojko Bibic 8 0

I and then I can to do sum column

Goran Bibic 30-Apr-18 5:13am

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_radni,
SUM (cast(prekovremeni as float)) as ukupno_prekovremeni
from prijava_radnika
group by radnik

Msg 8120, Level 16, State 1, Line 2
Column 'prijava_radnika.vrijemeprijave' 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 2
Column 'prijava_radnika.vrijemeodjave' 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 2
Column 'prijava_radnika.vrijemeprijave' 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 2
Column 'prijava_radnika.vrijemeodjave' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.