SUM COLUMN1(radni_sati)和COLUMN2(prekovremeni)ORDER by(radnik)SQL? [英] SUM COLUMN1(radni_sati) and COLUMN2(prekovremeni) ORDER by (radnik) SQL?
本文介绍了SUM COLUMN1(radni_sati)和COLUMN2(prekovremeni)ORDER by(radnik)SQL?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
sql工作正常的代码
我需要总和列radni sati和列prekovremeni sati
SUM(cast(radni_sati as float))作为Ukpuno_radni_sati
group by radnik ......这样的......
有些帮助吗?
我需要结果(示例)列工作人员总数(radnik)
Nr radnik
radni_sati
prekovremeni
1
Gojko Bibic
69 21
2
Stevo Stevic
122 41
3 + ...等
我的尝试:
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测试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 2012年3月16日17: 23:20 2010年3月16日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 :00 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测试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 201 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测试bbb 06 Apr 2018 1 4:28:17 06 Apr 2018 11:38:56 2 NULL
2704 50测试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测试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测试RF Citac radnik 29 Apr 2018 13:18:52 29 Apr 2018 13:02:03 0 0
解决方案
很难弄清楚你在这里要做什么 - 你的解释很不清楚 - 但试试这个:
< pre lang =SQL> SELECT Worker,SUM(radni_sati),SUM(prekovremeni_sati)
FROM MyTable
GROUP BY 工人
如果这不是你想要做的,你需要更清楚地解释你想要实现的目标,可能是列名,样本输入和样本输出数据。
选择[radnik]作为Radnik,
SUM(施放((CASE WHEN DATEPART(WEEKDAY,vrijemeprijave)in(1,7)然后0 else(CASE WHEN(DATEDIFF(SECOND,vrijemeprijave,vrijemeodjave)) / 3600)> 8那8 ELSE(DATEDIFF(第二,vrijemeprijave,vrijemeodjave)/ 3600)END)结束)浮动))''Radni sati',
SUM(施放((例如,当DATEPART(WEEKDAY,vrijemeodjave)在(1) ,7)然后(DATEDIFF(第二,vrijemeprijave,vrijemeodjave)/ 3600)其他(案件时(DATEDIFF(第二,vrijemeprijave,vrijemeodjave)/ 3600)> 8然后((DATEDIFF(第二,vrijemeprijave,vrijemeodjave)/ 3600) - 8)ELSE NULL END)end)as float))AS'Prekovremeni'
来自[prijava_radnika]
group by [radnik]
解决了......谢谢所有人......
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
解决方案
It's difficult to work out what you are trying to do here - your explanation is very unclear - but try this:
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.
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...
这篇关于SUM COLUMN1(radni_sati)和COLUMN2(prekovremeni)ORDER by(radnik)SQL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文