SQLite GROUP BY Challenges for SUM and TOTAL

Question

I have a simple query:

SELECT
    DateAndTime,
    count(Load), min(Load), max(Load), sum(Load)
FROM
    UpdatedRegions
GROUP BY
    DateAndTime
ORDER BY
    DateAndTime;

As you can see, I'm trying to get info about the variable "Load" at each timestep. Example data is below:

DateAndTime Load
2028-01-01 00:00:00 9,382.3938
2028-01-01 00:00:00 4,662.4381
2028-01-01 00:00:00 2,328.2232
2028-01-01 00:00:00 815.0305
2028-01-01 00:00:00 6,118.7683
2028-01-01 00:00:00 1,791.7472
2028-01-01 00:00:00 228.7977
2028-01-01 01:00:00 8,865.7735
2028-01-01 01:00:00 4,436.1125
2028-01-01 01:00:00 2,236.8587
2028-01-01 01:00:00 789.5337
2028-01-01 01:00:00 5,787.2574
2028-01-01 01:00:00 1,712.3446

The problem is that I'm getting unexplainable results for SUM and TOTAL. While the count, min, and max are correct, it returns 1065.8282 for the SUM at 2028-01-01 00:00:00. I get the same strange answer if I use TOTAL. It seems like my GROUP BY clause is working since it return count, min, and max correctly.

What am I doing wrong? I tried this in two different versions of SQLite (3.13.0 and 3.41.2).

Lajos Arpad · Accepted Answer · 2024-07-29 16:45:00Z

0

The issue is that , is not recognized as a thousand separator. Try:

select 9,382.3938 + 4,662.4381 as foo, 9382.3938 + 4662.4381 as bar;

you will get

662.4381|14044.8319

as result, the first addition is correct, the second is not. Wrap cast(replace(col, ',', '') as float) around your columns, like:

SELECT
    DateAndTime,
    count(Load), min(Load), max(Load), sum(cast(replace(Load, ',', '') as float))
FROM
    UpdatedRegions
GROUP BY
    DateAndTime
ORDER BY
    DateAndTime;

min and max are unreliable for the same reason, so you will likely need to apply the same pattern for them too.

answered Jul 29, 2024 at 16:45

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lajos Arpad Over a year ago

@sammy happy to help! If this answer solved the problem for you, then you might consider accepting it as the correct answer.

Collectives™ on Stack Overflow

SQLite GROUP BY Challenges for SUM and TOTAL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related