select productcd, convert(int,(datediff(n,min(a.starttime),max(a.endtime)))) as totoal_time from prog102 a

where a.endtime between '2008-03-10 08:30' and '2008-03-11 08:30'

group by productcd

이 경우는

품목                          소요시간

MTL-004A                  468

MTL-063A                   1227

MTL-120AP 1073

MTL-121AP 2851

MTL-127AP 478

MTLC01                1430

MTLC02                 1115

total time 값이 group by 되서 합으로 나와야 합니다. 그렇게 되는거 같구요..

하지만... 각각 startime,endtime을 따로 아래와 같이 나타내서 각각 더 해 보면..위의 품목의 총소요시간과

차이가 납니다.

select productcd, convert(int,(datediff(n,min(a.starttime),max(a.endtime)))) as totoal_time, starttime,endtime from

prog102 a

where a.endtime between '2008-03-10 08:30' and '2008-03-11 08:30'

group by productcd,starttime,endtime

MTL-004A 29 2008-03-10 09:18:00 2008-03-10 09:47:00

MTL-004A 33 2008-03-10 09:18:00 2008-03-10 09:51:00

MTL-004A 30 2008-03-10 09:20:00 2008-03-10 09:50:00

MTL-004A 32 2008-03-10 09:20:00 2008-03-10 09:52:00

MTL-004A 8 2008-03-10 09:50:00 2008-03-10 09:58:00

MTL-004A 13 2008-03-10 09:51:00 2008-03-10 10:04:00

MTL-004A 31 2008-03-10 09:52:00 2008-03-10 10:23:00

MTL-004A 12 2008-03-10 09:53:00 2008-03-10 10:05:00

MTL-004A 13 2008-03-10 09:53:00 2008-03-10 10:06:00

MTL-004A 23 2008-03-10 10:06:00 2008-03-10 10:29:00

MTL-004A 29 2008-03-10 10:07:00 2008-03-10 10:36:00

MTL-004A 18 2008-03-10 10:25:00 2008-03-10 10:43:00

MTL-004A 44 2008-03-10 10:25:00 2008-03-10 11:09:00

MTL-004A 38 2008-03-10 10:27:00 2008-03-10 11:05:00

MTL-004A 42 2008-03-10 10:31:00 2008-03-10 11:13:00

MTL-004A 43 2008-03-10 10:31:00 2008-03-10 11:14:00

MTL-004A 31 2008-03-10 10:38:00 2008-03-10 11:09:00

MTL-004A 1 2008-03-10 11:09:00 2008-03-10 11:10:00

MTL-004A 23 2008-03-10 11:16:00 2008-03-10 11:39:00

MTL-004A 24 2008-03-10 11:16:00 2008-03-10 11:40:00

MTL-004A 25 2008-03-10 11:16:00 2008-03-10 11:41:00

MTL-004A 11 2008-03-10 11:45:00 2008-03-10 11:56:00

MTL-004A 14 2008-03-10 11:45:00 2008-03-10 11:59:00

MTL-004A 13 2008-03-10 13:13:00 2008-03-10 13:26:00

MTL-004A 16 2008-03-10 13:28:00 2008-03-10 13:44:00

MTL-004A 21 2008-03-10 13:45:00 2008-03-10 14:06:00

MTL-004A 28 2008-03-10 13:45:00 2008-03-10 14:13:00

MTL-004A 23 2008-03-10 14:07:00 2008-03-10 14:30:00

MTL-004A 10 2008-03-10 14:11:00 2008-03-10 14:21:00

MTL-004A 9 2008-03-10 14:14:00 2008-03-10 14:23:00

MTL-004A 8 2008-03-10 14:32:00 2008-03-10 14:40:00

MTL-004A 36 2008-03-10 14:35:00 2008-03-10 15:11:00

MTL-004A 9 2008-03-10 14:41:00 2008-03-10 14:50:00

MTL-004A 8 2008-03-10 14:50:00 2008-03-10 14:58:00

MTL-004A 12 2008-03-10 15:21:00 2008-03-10 15:33:00

MTL-004A 11 2008-03-10 15:22:00 2008-03-10 15:33:00

MTL-004A 41 2008-03-10 15:22:00 2008-03-10 16:03:00

MTL-004A 42 2008-03-10 15:22:00 2008-03-10 16:04:00

MTL-004A 7 2008-03-10 15:34:00 2008-03-10 15:41:00

MTL-004A 26 2008-03-10 15:34:00 2008-03-10 16:00:00

MTL-004A 7 2008-03-10 15:41:00 2008-03-10 15:48:00

MTL-004A 6 2008-03-10 15:54:00 2008-03-10 16:00:00

MTL-004A 6 2008-03-10 16:01:00 2008-03-10 16:07:00

MTL-004A 11 2008-03-10 16:01:00 2008-03-10 16:12:00

MTL-004A 14 2008-03-10 16:04:00 2008-03-10 16:18:00

MTL-004A 7 2008-03-10 16:13:00 2008-03-10 16:20:00

MTL-004A 29 2008-03-10 16:19:00 2008-03-10 16:48:00

MTL-004A 17 2008-03-10 16:49:00 2008-03-10 17:06:00

MTL-063A 66 2008-03-10 08:39:00 2008-03-10 09:45:00

MTL-063A 37 2008-03-10 08:41:00 2008-03-10 09:18:00

MTL-063A 103 2008-03-10 09:10:00 2008-03-10 10:53:00

MTL-063A 35 2008-03-10 09:59:00 2008-03-10 10:34:00

MTL-063A 36 2008-03-10 09:59:00 2008-03-10 10:35:00

MTL-063A 68 2008-03-10 10:34:00 2008-03-10 11:42:00

MTL-063A 47 2008-03-10 10:35:00 2008-03-10 11:22:00

MTL-063A 48 2008-03-10 10:35:00 2008-03-10 11:23:00

MTL-063A 30 2008-03-10 10:36:00 2008-03-10 11:06:00

MTL-063A 30 2008-03-10 10:37:00 2008-03-10 11:07:00

MTL-063A 30 2008-03-10 10:38:00 2008-03-10 11:08:00

MTL-063A 43 2008-03-10 10:47:00 2008-03-10 11:30:00

MTL-063A 25 2008-03-10 10:54:00 2008-03-10 11:19:00

MTL-063A 79 2008-03-10 10:57:00 2008-03-10 12:16:00

MTL-063A 49 2008-03-10 11:05:00 2008-03-10 11:54:00

MTL-063A 24 2008-03-10 11:07:00 2008-03-10 11:31:00

MTL-063A 19 2008-03-10 11:08:00 2008-03-10 11:27:00

MTL-063A 2 2008-03-10 11:24:00 2008-03-10 11:26:00

MTL-063A 30 2008-03-10 11:24:00 2008-03-10 11:54:00

MTL-063A 9 2008-03-10 11:28:00 2008-03-10 11:37:00

MTL-063A 95 2008-03-10 11:28:00 2008-03-10 13:03:00

MTL-063A 116 2008-03-10 11:28:00 2008-03-10 13:24:00

MTL-063A 20 2008-03-10 11:30:00 2008-03-10 11:50:00

MTL-063A 90 2008-03-10 11:30:00 2008-03-10 13:00:00

MTL-063A 29 2008-03-10 11:32:00 2008-03-10 12:01:00

MTL-063A 22 2008-03-10 11:38:00 2008-03-10 12:00:00

MTL-063A 114 2008-03-10 11:52:00 2008-03-10 13:46:00

MTL-063A 83 2008-03-10 11:55:00 2008-03-10 13:18:00

MTL-063A 105 2008-03-10 12:02:00 2008-03-10 13:47:00

MTL-063A 49 2008-03-10 13:01:00 2008-03-10 13:50:00

MTL-063A 18 2008-03-10 13:03:00 2008-03-10 13:21:00

MTL-063A 31 2008-03-10 13:03:00 2008-03-10 13:34:00

MTL-063A 16 2008-03-10 13:04:00 2008-03-10 13:20:00

MTL-063A 22 2008-03-10 13:06:00 2008-03-10 13:28:00

MTL-063A 19 2008-03-10 13:20:00 2008-03-10 13:39:00

MTL-063A 43 2008-03-10 13:20:00 2008-03-10 14:03:00

MTL-063A 14 2008-03-10 13:24:00 2008-03-10 13:38:00

MTL-063A 38 2008-03-10 13:28:00 2008-03-10 14:06:00

MTL-063A 77 2008-03-10 13:28:00 2008-03-10 14:45:00

MTL-063A 25 2008-03-10 13:29:00 2008-03-10 13:54:00

MTL-063A 24 2008-03-10 13:40:00 2008-03-10 14:04:00

MTL-063A 1 2008-03-10 13:42:00 2008-03-10 13:43:00

MTL-063A 16 2008-03-10 13:44:00 2008-03-10 14:00:00

MTL-063A 18 2008-03-10 13:45:00 2008-03-10 14:03:00

MTL-063A 11 2008-03-10 13:47:00 2008-03-10 13:58:00

MTL-063A 55 2008-03-10 13:50:00 2008-03-10 14:45:00

MTL-063A 27 2008-03-10 13:56:00 2008-03-10 14:23:00

MTL-063A 12 2008-03-10 13:59:00 2008-03-10 14:11:00

MTL-063A 35 2008-03-10 14:01:00 2008-03-10 14:36:00

MTL-063A 34 2008-03-10 14:02:00 2008-03-10 14:36:00

MTL-063A 24 2008-03-10 14:05:00 2008-03-10 14:29:00

MTL-063A 14 2008-03-10 14:07:00 2008-03-10 14:21:00

MTL-063A 9 2008-03-10 14:13:00 2008-03-10 14:22:00

MTL-063A 77 2008-03-10 14:13:00 2008-03-10 15:30:00

MTL-063A 78 2008-03-10 14:13:00 2008-03-10 15:31:00

MTL-063A 0 2008-03-10 14:20:00 2008-03-10 14:20:00

MTL-063A 12 2008-03-10 14:22:00 2008-03-10 14:34:00

MTL-063A 45 2008-03-10 14:24:00 2008-03-10 15:09:00

MTL-063A 8 2008-03-10 14:25:00 2008-03-10 14:33:00

MTL-063A 66 2008-03-10 14:25:00 2008-03-10 15:31:00

MTL-063A 22 2008-03-10 14:30:00 2008-03-10 14:52:00

MTL-063A 10 2008-03-10 14:36:00 2008-03-10 14:46:00

MTL-063A 52 2008-03-10 14:37:00 2008-03-10 15:29:00

MTL-063A 13 2008-03-10 14:38:00 2008-03-10 14:51:00

MTL-063A 56 2008-03-10 14:42:00 2008-03-10 15:38:00

MTL-063A 65 2008-03-10 14:46:00 2008-03-10 15:51:00

MTL-063A 104 2008-03-10 14:50:00 2008-03-10 16:34:00

MTL-063A 15 2008-03-10 14:51:00 2008-03-10 15:06:00

MTL-063A 57 2008-03-10 14:51:00 2008-03-10 15:48:00

MTL-063A 9 2008-03-10 14:52:00 2008-03-10 15:01:00

MTL-063A 18 2008-03-10 14:52:00 2008-03-10 15:10:00

MTL-063A 27 2008-03-10 15:07:00 2008-03-10 15:34:00

MTL-063A 97 2008-03-10 15:07:00 2008-03-10 16:44:00

MTL-063A 27 2008-03-10 15:08:00 2008-03-10 15:35:00

MTL-063A 43 2008-03-10 15:32:00 2008-03-10 16:15:00

MTL-063A 57 2008-03-10 15:33:00 2008-03-10 16:30:00

MTL-063A 42 2008-03-10 15:34:00 2008-03-10 16:16:00

MTL-063A 43 2008-03-10 15:34:00 2008-03-10 16:17:00

MTL-063A 42 2008-03-10 15:35:00 2008-03-10 16:17:00

MTL-063A 10 2008-03-10 15:37:00 2008-03-10 15:47:00

MTL-063A 14 2008-03-10 15:48:00 2008-03-10 16:02:00

MTL-063A 1 2008-03-10 15:49:00 2008-03-10 15:50:00

MTL-063A 1 2008-03-10 15:50:00 2008-03-10 15:51:00

MTL-063A 87 2008-03-10 15:52:00 2008-03-10 17:19:00

MTL-063A 48 2008-03-10 15:54:00 2008-03-10 16:42:00

MTL-063A 11 2008-03-10 15:58:00 2008-03-10 16:09:00

MTL-063A 16 2008-03-10 16:03:00 2008-03-10 16:19:00

MTL-063A 10 2008-03-10 16:10:00 2008-03-10 16:20:00

MTL-063A 42 2008-03-10 16:17:00 2008-03-10 16:59:00

MTL-063A 32 2008-03-10 16:18:00 2008-03-10 16:50:00

MTL-063A 30 2008-03-10 16:19:00 2008-03-10 16:49:00

MTL-063A 15 2008-03-10 16:20:00 2008-03-10 16:35:00

MTL-063A 15 2008-03-10 16:22:00 2008-03-10 16:37:00

MTL-063A 18 2008-03-10 16:27:00 2008-03-10 16:45:00

MTL-063A 41 2008-03-10 16:31:00 2008-03-10 17:12:00

MTL-063A 36 2008-03-10 16:32:00 2008-03-10 17:08:00

MTL-063A 7 2008-03-10 16:47:00 2008-03-10 16:54:00

MTL-063A 19 2008-03-10 16:51:00 2008-03-10 17:10:00

MTL-063A 46 2008-03-10 16:51:00 2008-03-10 17:37:00

MTL-063A 1 2008-03-10 17:36:00 2008-03-10 17:37:00

MTL-063A 95 2008-03-10 20:45:00 2008-03-10 22:20:00

MTL-063A 102 2008-03-10 22:20:00 2008-03-11 00:02:00

MTL-063A 80 2008-03-11 01:00:00 2008-03-11 02:20:00

MTL-063A 96 2008-03-11 02:20:00 2008-03-11 03:56:00

MTL-063A 67 2008-03-11 03:59:00 2008-03-11 05:06:00

MTL-120AP 72 2008-03-10 10:54:00 2008-03-10 12:06:00

MTL-120AP 92 2008-03-10 13:29:00 2008-03-10 15:01:00

MTL-120AP 72 2008-03-10 15:34:00 2008-03-10 16:46:00

MTL-120AP 136 2008-03-10 20:53:00 2008-03-10 23:09:00

MTL-120AP 137 2008-03-10 23:15:00 2008-03-11 01:32:00

MTL-120AP 111 2008-03-11 01:32:00 2008-03-11 03:23:00

MTL-120AP 78 2008-03-11 03:29:00 2008-03-11 04:47:00

MTL-121AP 2505 2008-03-08 17:18:00 2008-03-10 11:03:00

MTL-121AP 17 2008-03-10 16:23:00 2008-03-10 16:40:00

MTL-121AP 7 2008-03-10 16:42:00 2008-03-10 16:49:00

MTL-127AP 68 2008-03-10 21:27:00 2008-03-10 22:35:00

MTL-127AP 77 2008-03-10 22:49:00 2008-03-11 00:06:00

MTL-127AP 61 2008-03-11 01:19:00 2008-03-11 02:20:00

MTL-127AP 95 2008-03-11 02:34:00 2008-03-11 04:09:00

MTL-127AP 65 2008-03-11 04:20:00 2008-03-11 05:25:00

MTLC01 135 2008-03-10 08:30:00 2008-03-10 10:45:00

MTLC01 21 2008-03-10 08:35:00 2008-03-10 08:56:00

MTLC01 190 2008-03-10 08:35:00 2008-03-10 11:45:00

MTLC01 129 2008-03-10 08:37:00 2008-03-10 10:46:00

MTLC01 197 2008-03-10 08:37:00 2008-03-10 11:54:00

MTLC01 169 2008-03-10 08:39:00 2008-03-10 11:28:00

MTLC01 75 2008-03-10 08:40:00 2008-03-10 09:55:00

MTLC01 80 2008-03-10 08:40:00 2008-03-10 10:00:00

MTLC01 27 2008-03-10 08:58:00 2008-03-10 09:25:00

MTLC01 126 2008-03-10 09:25:00 2008-03-10 11:31:00

MTLC01 31 2008-03-10 09:29:00 2008-03-10 10:00:00

MTLC01 86 2008-03-10 10:05:00 2008-03-10 11:31:00

MTLC01 84 2008-03-10 10:21:00 2008-03-10 11:45:00

MTLC01 35 2008-03-10 10:25:00 2008-03-10 11:00:00

MTLC01 167 2008-03-10 10:49:00 2008-03-10 13:36:00

MTLC01 185 2008-03-10 10:49:00 2008-03-10 13:54:00

MTLC01 27 2008-03-10 11:01:00 2008-03-10 11:28:00

MTLC01 50 2008-03-10 11:10:00 2008-03-10 12:00:00

MTLC01 30 2008-03-10 11:30:00 2008-03-10 12:00:00

MTLC01 216 2008-03-10 11:33:00 2008-03-10 15:09:00

MTLC01 229 2008-03-10 11:42:00 2008-03-10 15:31:00

MTLC01 254 2008-03-10 11:56:00 2008-03-10 16:10:00

MTLC01 300 2008-03-10 12:00:00 2008-03-10 17:00:00

MTLC01 31 2008-03-10 13:20:00 2008-03-10 13:51:00

MTLC01 200 2008-03-10 13:29:00 2008-03-10 16:49:00

MTLC01 77 2008-03-10 13:40:00 2008-03-10 14:57:00

MTLC01 31 2008-03-10 13:52:00 2008-03-10 14:23:00

MTLC01 31 2008-03-10 14:24:00 2008-03-10 14:55:00

MTLC01 31 2008-03-10 15:21:00 2008-03-10 15:52:00

MTLC01 26 2008-03-10 15:54:00 2008-03-10 16:20:00

MTLC01 26 2008-03-10 16:21:00 2008-03-10 16:47:00

MTLC01 31 2008-03-10 16:48:00 2008-03-10 17:19:00

MTLC01 23 2008-03-10 18:02:00 2008-03-10 18:25:00

MTLC01 134 2008-03-10 18:06:00 2008-03-10 20:20:00

MTLC01 28 2008-03-10 18:26:00 2008-03-10 18:54:00

MTLC01 38 2008-03-10 18:55:00 2008-03-10 19:33:00

MTLC01 73 2008-03-10 19:07:00 2008-03-10 20:20:00

MTLC01 0 2008-03-10 19:16:00 2008-03-10 19:16:00

MTLC01 33 2008-03-10 19:34:00 2008-03-10 20:07:00

MTLC01 198 2008-03-10 20:42:00 2008-03-11 00:00:00

MTLC01 191 2008-03-10 20:48:00 2008-03-10 23:59:00

MTLC01 257 2008-03-10 20:56:00 2008-03-11 01:13:00

MTLC01 32 2008-03-10 21:00:00 2008-03-10 21:32:00

MTLC01 662 2008-03-10 21:18:00 2008-03-11 08:20:00

MTLC01 250 2008-03-11 01:20:00 2008-03-11 05:30:00

MTLC01 90 2008-03-11 01:40:00 2008-03-11 03:10:00

MTLC01 140 2008-03-11 01:40:00 2008-03-11 04:00:00

MTLC01 160 2008-03-11 02:50:00 2008-03-11 05:30:00

MTLC01 75 2008-03-11 07:00:00 2008-03-11 08:15:00

MTLC02 200 2008-03-10 10:20:00 2008-03-10 13:40:00

MTLC02 131 2008-03-10 11:33:00 2008-03-10 13:44:00

MTLC02 198 2008-03-10 11:42:00 2008-03-10 15:00:00

MTLC02 135 2008-03-10 13:44:00 2008-03-10 15:59:00

MTLC02 129 2008-03-10 13:45:00 2008-03-10 15:54:00

MTLC02 91 2008-03-10 13:59:00 2008-03-10 15:30:00

MTLC02 205 2008-03-10 15:03:00 2008-03-10 18:28:00

MTLC02 203 2008-03-10 15:04:00 2008-03-10 18:27:00

MTLC02 85 2008-03-10 16:00:00 2008-03-10 17:25:00

MTLC02 226 2008-03-10 16:41:00 2008-03-10 20:27:00

MTLC02 33 2008-03-10 16:45:00 2008-03-10 17:18:00

MTLC02 70 2008-03-10 17:23:00 2008-03-10 18:33:00

MTLC02 129 2008-03-10 18:00:00 2008-03-10 20:09:00

MTLC02 132 2008-03-10 18:00:00 2008-03-10 20:12:00

MTLC02 32 2008-03-10 18:19:00 2008-03-10 18:51:00

MTLC02 50 2008-03-10 18:40:00 2008-03-10 19:30:00

MTLC02 41 2008-03-10 19:33:00 2008-03-10 20:14:00

MTLC02 109 2008-03-10 20:46:00 2008-03-10 22:35:00

MTLC02 126 2008-03-10 20:47:00 2008-03-10 22:53:00

MTLC02 140 2008-03-10 21:40:00 2008-03-11 00:00:00

MTLC02 180 2008-03-10 22:35:00 2008-03-11 01:35:00

MTLC02 146 2008-03-10 22:55:00 2008-03-11 01:21:00

MTLC02 95 2008-03-11 01:03:00 2008-03-11 02:38:00

MTLC02 77 2008-03-11 02:40:00 2008-03-11 03:57:00

MTLC02 65 2008-03-11 03:50:00 2008-03-11 04:55:00

왜 이럴까요?

첫번째 쿼리 처럼 group by 하면.. 그게 총 소요 시간 아닌가요?

sum 을 사용 필요가 있나요?

sum을 이용해야 한다면. 어떻게 해야 할지요?

답변 부탁드립니다. 감사합니다.