Grouping and calculating sum by one query-CodePudding

I have simple database for store data: kind of movement (walking, run etc.), distance, time stamp. I want generate report and calculate sum of distance for all records, group by kind of movement and calculate sum for each kind, group by day and calculate sum for each day.

But by one query I can get only one kind of sum: without group - total. If I use "GROUP BY" parameter then total sum is absent just for group records.

If use two groups I can not get sum for previous group (for kind of move).

So, need I use several queries or I can do it by one?

I use method of SQLiteDatabase query().

And I have no idea how by cursor get the first group and then the second and so on.

CodePudding user response：

If you need sum(distance) by movement_kind and sum(distance) by movement_date with a single query. You may want to use SQLiteDatabase.rawQuery() with a query similar to the following:

select movement_kind, 
       null as movement_date, 
       sum(distance) as distance 
  from s7 
 group by movement_kind
union all
select null, 
       movement_date, 
       sum(distance) 
  from s7 
 group by movement_date;

The result looks like this:

movement_kind|movement_date|distance|
------------- ------------- -------- 
run          |             |     6.0|
walk         |             |    15.0|
             |   2022-12-01|     5.0|
             |   2022-12-02|     3.0|
             |   2022-12-03|     4.0|
             |   2022-12-04|     4.0|
             |   2022-12-05|     5.0|

In your application code, you need to check if movement_kind or movement_date is NULL. If so, value in distance is group by the other column.

Sample data:

create table s7 (
    movement_kind   text,
    distance        float,
    movement_date   date);
    

insert into s7
values
('walk', 5, '2022-12-01'),
('walk', 3, '2022-12-02'),
('walk', 1, '2022-12-03'),
('walk', 2, '2022-12-04'),
('walk', 4, '2022-12-05'),
('run' , 3, '2022-12-03'),
('run' , 2, '2022-12-04'),
('run' , 1, '2022-12-05');