SQL, Group By statement

Hi,

I have a database with the following querry:

Select * --DateandTime,max(PackWeights.[Index])As Idx,Pk,PackWeights.Pack
FROM [CheckWeighers].[dbo].[PackWeights]
Where DateandTime between '2015-05-04 0:00:00.00' and '2015-05-06 0:00:00.00'
Order By DateandTime Asc
 which gives the following result:

DateandTime                  Index      Pack      pk
2015-05-04 09:53:59.000      1      1001.5      2015/05/04 09:53:591001.50
2015-05-04 09:53:59.000      36      1004.0      2015/05/04 09:53:591004.035
2015-05-04 09:53:59.000      47      1011.0      2015/05/04 09:53:591011.046
2015-05-04 09:53:59.000      24      1012.9      2015/05/04 09:53:591012.923
2015-05-04 09:53:59.000      23      1023.1      2015/05/04 09:53:591023.122
2015-05-04 09:53:59.000      58      1028.7      2015/05/04 09:53:591028.757
2015-05-04 09:53:59.000      35      1048.7      2015/05/04 09:53:591048.734
2015-05-04 09:53:59.000      65      1055.8      2015/05/04 09:53:591055.864
2015-05-04 09:53:59.000      46      1130.2      2015/05/04 09:53:591130.245
2015-05-04 09:53:59.000      57      1198.4      2015/05/04 09:53:591198.456
2015-05-04 09:53:59.000      56      1210.7      2015/05/04 09:53:591210.755
2015-05-04 09:53:59.000      7      708.1      2015/05/04 09:53:59708.16
2015-05-04 09:53:59.000      12      712.2      2015/05/04 09:53:59712.211
2015-05-04 09:53:59.000      19      717.7      2015/05/04 09:53:59717.718
2015-05-04 09:53:59.000      20      720.6      2015/05/04 09:53:59720.619
2015-05-04 09:53:59.000      13      730.5      2015/05/04 09:53:59730.512
2015-05-04 09:53:59.000      14      741.3      2015/05/04 09:53:59741.313
2015-05-04 09:53:59.000      21      743.5      2015/05/04 09:53:59743.520
2015-05-04 09:53:59.000      33      744.0      2015/05/04 09:53:59744.032
2015-05-04 09:53:59.000      26      747.7      2015/05/04 09:53:59747.725
2015-05-04 09:53:59.000      38      749.3      2015/05/04 09:53:59749.337
2015-05-04 09:53:59.000      11      750.1      2015/05/04 09:53:59750.110
2015-05-04 09:53:59.000      27      754.8      2015/05/04 09:53:59754.826
2015-05-04 09:53:59.000      39      761.5      2015/05/04 09:53:59761.538
2015-05-04 09:53:59.000      18      768.1      2015/05/04 09:53:59768.117
2015-05-04 09:53:59.000      40      778.4      2015/05/04 09:53:59778.439
2015-05-04 09:53:59.000      43      781.0      2015/05/04 09:53:59781.042
2015-05-04 09:53:59.000      32      781.6      2015/05/04 09:53:59781.631
2015-05-04 09:53:59.000      28      785.3      2015/05/04 09:53:59785.327
2015-05-04 09:53:59.000      44      786.2      2015/05/04 09:53:59786.243
2015-05-04 09:53:59.000      5      787.7      2015/05/04 09:53:59787.74
2015-05-04 09:53:59.000      42      805.9      2015/05/04 09:53:59805.941
2015-05-04 09:53:59.000      37      814.7      2015/05/04 09:53:59814.736
2015-05-04 09:53:59.000      41      816.7      2015/05/04 09:53:59816.740
2015-05-04 09:53:59.000      49      818.4      2015/05/04 09:53:59818.448
2015-05-04 09:53:59.000      31      822.0      2015/05/04 09:53:59822.030
2015-05-04 09:53:59.000      15      830.8      2015/05/04 09:53:59830.814
2015-05-04 09:53:59.000      22      833.5      2015/05/04 09:53:59833.521
2015-05-04 09:53:59.000      48      835.0      2015/05/04 09:53:59835.047
2015-05-04 09:53:59.000      50      839.4      2015/05/04 09:53:59839.449
2015-05-04 09:53:59.000      25      846.8      2015/05/04 09:53:59846.824
2015-05-04 09:53:59.000      8      850.1      2015/05/04 09:53:59850.17
2015-05-04 09:53:59.000      51      860.1      2015/05/04 09:53:59860.150
2015-05-04 09:53:59.000      52      893.9      2015/05/04 09:53:59893.951
2015-05-04 09:53:59.000      53      895.7      2015/05/04 09:53:59895.752
2015-05-04 09:53:59.000      54      908.2      2015/05/04 09:53:59908.253
2015-05-04 09:53:59.000      55      915.1      2015/05/04 09:53:59915.154
2015-05-04 09:53:59.000      60      916.0      2015/05/04 09:53:59916.059
2015-05-04 09:53:59.000      6      923.6      2015/05/04 09:53:59923.65
2015-05-04 09:53:59.000      59      924.3      2015/05/04 09:53:59924.358
2015-05-04 09:53:59.000      3      926.2      2015/05/04 09:53:59926.22
2015-05-04 09:53:59.000      2      927.0      2015/05/04 09:53:59927.01
2015-05-04 09:53:59.000      34      933.6      2015/05/04 09:53:59933.633
2015-05-04 09:53:59.000      4      934.3      2015/05/04 09:53:59934.33
2015-05-04 09:53:59.000      29      934.9      2015/05/04 09:53:59934.928
2015-05-04 09:53:59.000      30      938.4      2015/05/04 09:53:59938.429
2015-05-04 09:53:59.000      10      941.5      2015/05/04 09:53:59941.59
2015-05-04 09:53:59.000      61      941.8      2015/05/04 09:53:59941.860
2015-05-04 09:53:59.000      63      948.0      2015/05/04 09:53:59948.062
2015-05-04 09:53:59.000      62      956.9      2015/05/04 09:53:59956.961
2015-05-04 09:53:59.000      9      960.3      2015/05/04 09:53:59960.38
2015-05-04 09:53:59.000      17      962.8      2015/05/04 09:53:59962.816
2015-05-04 09:53:59.000      64      963.9      2015/05/04 09:53:59963.963
2015-05-04 09:53:59.000      16      980.9      2015/05/04 09:53:59980.915
2015-05-04 09:53:59.000      45      992.2      2015/05/04 09:53:59992.244
2015-05-04 10:13:30.000      1      0.0      2015/05/04 10:13:300.00
2015-05-04 10:13:30.000      2      0.0      2015/05/04 10:13:300.01
2015-05-04 10:54:03.000      1      1215.9      2015/05/04 10:54:031215.90
2015-05-04 10:55:16.000      1      1226.7      2015/05/04 10:55:161226.70
2015-05-04 10:55:16.000      3      1263.7      2015/05/04 10:55:161263.72
2015-05-04 10:55:16.000      2      1282.1      2015/05/04 10:55:161282.11
2015-05-04 10:55:34.000      5      1236.5      2015/05/04 10:55:341236.54
2015-05-04 10:55:34.000      6      1244.9      2015/05/04 10:55:341244.95
2015-05-04 10:55:34.000      4      1247.9      2015/05/04 10:55:341247.93
2015-05-04 10:55:34.000      7      1252.2      2015/05/04 10:55:341252.26
2015-05-04 10:55:34.000      3      1257.6      2015/05/04 10:55:341257.62
2015-05-04 10:55:34.000      8      1261.3      2015/05/04 10:55:341261.37
2015-05-04 10:55:34.000      2      1266.9      2015/05/04 10:55:341266.91
2015-05-04 10:55:34.000      9      1268.7      2015/05/04 10:55:341268.78
2015-05-04 10:55:34.000      1      1270.1      2015/05/04 10:55:341270.10
2015-05-04 10:58:01.000      3      1259.1      2015/05/04 10:58:011259.12
2015-05-04 10:58:01.000      1      1268.5      2015/05/04 10:58:011268.50
2015-05-04 10:58:01.000      2      1296.7      2015/05/04 10:58:011296.71
2015-05-04 10:58:19.000      2      1259.8      2015/05/04 10:58:191259.81
2015-05-04 10:58:19.000      3      1265.4      2015/05/04 10:58:191265.42
2015-05-04 10:58:19.000      1      1267.5      2015/05/04 10:58:191267.50
2015-05-04 10:58:19.000      4      1271.4      2015/05/04 10:58:191271.43
2015-05-04 10:58:19.000      5      1276.2      2015/05/04 10:58:191276.24
2015-05-04 10:58:19.000      6      1279.0      2015/05/04 10:58:191279.05
2015-05-04 11:01:05.000      1      1276.6      2015/05/04 11:01:051276.60
2015-05-04 11:01:23.000      4      1241.1      2015/05/04 11:01:231241.13
2015-05-04 11:01:23.000      5      1241.6      2015/05/04 11:01:231241.64
2015-05-04 11:01:23.000      6      1249.7      2015/05/04 11:01:231249.75
2015-05-04 11:01:23.000      3      1250.2      2015/05/04 11:01:231250.22
2015-05-04 11:01:23.000      2      1263.1      2015/05/04 11:01:231263.11
2015-05-04 11:01:23.000      7      1263.3      2015/05/04 11:01:231263.36
2015-05-04 11:01:23.000      8      1272.4      2015/05/04 11:01:231272.47
2015-05-04 11:01:23.000      1      1281.3      2015/05/04 11:01:231281.30
2015-05-04 11:01:23.000      12      1285.7      2015/05/04 11:01:231285.711
2015-05-04 11:01:23.000      10      1286.0      2015/05/04 11:01:231286.09
2015-05-04 11:01:23.000      9      1288.4      2015/05/04 11:01:231288.48
2015-05-04 11:01:23.000      11      1293.3      2015/05/04 11:01:231293.310
2015-05-04 11:01:41.000      14      1263.9      2015/05/04 11:01:411263.913
2015-05-04 11:01:41.000      12      1268.2      2015/05/04 11:01:411268.211
2015-05-04 11:01:41.000      13      1270.4      2015/05/04 11:01:411270.412
2015-05-04 11:01:41.000      11      1275.3      2015/05/04 11:01:411275.310
2015-05-04 11:01:41.000      7      1275.8      2015/05/04 11:01:411275.86
2015-05-04 11:01:41.000      8      1278.4      2015/05/04 11:01:411278.47
2015-05-04 11:01:41.000      10      1280.2      2015/05/04 11:01:411280.29
2015-05-04 11:01:41.000      9      1281.0      2015/05/04 11:01:411281.08
2015-05-04 11:01:41.000      5      1281.9      2015/05/04 11:01:411281.94
2015-05-04 11:01:41.000      6      1284.9      2015/05/04 11:01:411284.95
2015-05-04 11:01:41.000      2      1293.3      2015/05/04 11:01:411293.31
2015-05-04 11:01:41.000      3      1294.2      2015/05/04 11:01:411294.22
2015-05-04 11:01:41.000      4      1297.0      2015/05/04 11:01:411297.03
2015-05-04 11:01:41.000      1      1297.1      2015/05/04 11:01:411297.10
2015-05-04 11:01:59.000      4      1260.4      2015/05/04 11:01:591260.43
2015-05-04 11:01:59.000      2      1262.4      2015/05/04 11:01:591262.41
2015-05-04 11:01:59.000      1      1262.9      2015/05/04 11:01:591262.90
2015-05-04 11:01:59.000      3      1264.4      2015/05/04 11:01:591264.42
2015-05-04 11:01:59.000      6      1267.0      2015/05/04 11:01:591267.05
2015-05-04 11:01:59.000      7      1268.4      2015/05/04 11:01:591268.46
2015-05-04 11:01:59.000      8      1271.5      2015/05/04 11:01:591271.57
2015-05-04 11:01:59.000      5      1271.6      2015/05/04 11:01:591271.64
2015-05-04 11:01:59.000      12      1272.3      2015/05/04 11:01:591272.311
2015-05-04 11:01:59.000      9      1273.9      2015/05/04 11:01:591273.98
2015-05-04 11:01:59.000      13      1274.1      2015/05/04 11:01:591274.112
2015-05-04 11:01:59.000      14      1275.2      2015/05/04 11:01:591275.213
2015-05-04 11:01:59.000      11      1276.0      2015/05/04 11:01:591276.010
2015-05-04 11:01:59.000      10      1276.0      2015/05/04 11:01:591276.09
2015-05-04 11:02:18.000      4      1274.7      2015/05/04 11:02:181274.73
2015-05-04 11:02:18.000      3      1276.7      2015/05/04 11:02:181276.72
2015-05-04 11:02:18.000      5      1280.6      2015/05/04 11:02:181280.64
2015-05-04 11:02:18.000      9      1282.6      2015/05/04 11:02:181282.68
2015-05-04 11:02:18.000      14      1282.7      2015/05/04 11:02:181282.713
2015-05-04 11:02:18.000      6      1285.4      2015/05/04 11:02:181285.45
2015-05-04 11:02:18.000      1      1285.9      2015/05/04 11:02:181285.90
2015-05-04 11:02:18.000      2      1286.2      2015/05/04 11:02:181286.21
2015-05-04 11:02:18.000      12      1286.3      2015/05/04 11:02:181286.311
2015-05-04 11:02:18.000      11      1288.2      2015/05/04 11:02:181288.210
2015-05-04 11:02:18.000      13      1288.4      2015/05/04 11:02:181288.412
2015-05-04 11:02:18.000      10      1288.5      2015/05/04 11:02:181288.59
2015-05-04 11:02:18.000      8      1288.7      2015/05/04 11:02:181288.77
2015-05-04 11:02:18.000      7      1289.9      2015/05/04 11:02:181289.96
2015-05-04 11:02:36.000      11      1264.0      2015/05/04 11:02:361264.010
2015-05-04 11:02:36.000      14      1267.7      2015/05/04 11:02:361267.713
2015-05-04 11:02:36.000      10      1268.7      2015/05/04 11:02:361268.79
2015-05-04 11:02:36.000      7      1269.1      2015/05/04 11:02:361269.16
2015-05-04 11:02:36.000      13      1270.0      2015/05/04 11:02:361270.012
2015-05-04 11:02:36.000      12      1270.7      2015/05/04 11:02:361270.711
2015-05-04 11:02:36.000      9      1272.2      2015/05/04 11:02:361272.28
2015-05-04 11:02:36.000      8      1273.1      2015/05/04 11:02:361273.17
2015-05-04 11:02:36.000      6      1274.1      2015/05/04 11:02:361274.15
2015-05-04 11:02:36.000      2      1280.2      2015/05/04 11:02:361280.21
2015-05-04 11:02:36.000      5      1281.1      2015/05/04 11:02:361281.14
2015-05-04 11:02:36.000      1      1283.5      2015/05/04 11:02:361283.50
2015-05-04 11:02:36.000      3      1290.0      2015/05/04 11:02:361290.02
2015-05-04 11:02:36.000      4      1291.6      2015/05/04 11:02:361291.63
2015-05-04 11:02:55.000      1      1272.8      2015/05/04 11:02:551272.80
2015-05-04 11:02:55.000      2      1277.2      2015/05/04 11:02:551277.21
2015-05-04 11:10:52.000      7      1254.4      2015/05/04 11:10:521254.46
2015-05-04 11:10:52.000      6      1268.0      2015/05/04 11:10:521268.05
2015-05-04 11:10:52.000      3      1275.4      2015/05/04 11:10:521275.42
2015-05-04 11:10:52.000      5      1276.2      2015/05/04 11:10:521276.24
2015-05-04 11:10:52.000      9      1277.5      2015/05/04 11:10:521277.58
2015-05-04 11:10:52.000      1      1283.2      2015/05/04 11:10:521283.20
2015-05-04 11:10:52.000      8      1284.6      2015/05/04 11:10:521284.67
2015-05-04 11:10:52.000      11      1286.2      2015/05/04 11:10:521286.210
2015-05-04 11:10:52.000      4      1287.0      2015/05/04 11:10:521287.03
2015-05-04 11:10:52.000      10      1287.2      2015/05/04 11:10:521287.29
2015-05-04 11:10:52.000      12      1287.3      2015/05/04 11:10:521287.311
2015-05-04 11:10:52.000      13      1299.2      2015/05/04 11:10:521299.212
2015-05-04 11:10:52.000      14      1302.0      2015/05/04 11:10:521302.013
2015-05-04 11:10:52.000      2      1311.3      2015/05/04 11:10:521311.31

I need the data grouped by DateAndTime and the Index column in ascending order while the DateAndTime groupped data is also on ascending order.Can someone please give an idea ?
Latzi_MarianSystems IntegratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
GROUP BY has nothing to do with sort order as sepecified with ORDER BY. So your question is not clear. Maybe you are looking for:

SELECT  DateandTime ,
        [Index] ,
        SUM(Pack)
FROM    [CheckWeighers].[dbo].[PackWeights]
WHERE   DateandTime BETWEEN '2015-05-04 0:00:00.00' AND '2015-05-06 0:00:00.00'
GROUP BY DateandTime ,
        [Index]
ORDER BY DateandTime ASC ,
        [Index] ASC;

Open in new window


but this looks not really correct, cause it seems that your [Index] is unique per [DateAndTime].

So, describe your desired output, please.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Latzi_MarianSystems IntegratorAuthor Commented:
the query above gives me :

Operand data type varchar is invalid for sum operator.
Latzi_MarianSystems IntegratorAuthor Commented:
SELECT  DateandTime ,
        [Index],
        Pack --,
        --SUM(Pack)
FROM    [CheckWeighers].[dbo].[PackWeights]
WHERE   DateandTime BETWEEN '2015-05-04 0:00:00.00' AND '2015-05-06 0:00:00.00'
GROUP BY DateandTime ,
        [Index],
        Pack
ORDER BY DateandTime ASC ,
        [Index] ASC;
       
       
        --ALTER TABLE [CheckWeighers].[dbo].[PackWeights]
--ALTER COLUMN [Index] NUMERIC(18,0)NULL

This works a charm :-)
PortletPaulEE Topic AdvisorCommented:
Be careful when using BETWEEN for date ranges.

If you want precisely 2 days of data (2015-05-04 & 2015-05-05) then you should use the following instead:

WHERE   DateandTime >= '20150504' AND  DateandTime < '20150506'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.