I am using SQLite3 with Python, and I have some data that looks roughly similar to this:
| Date | Parent ID | Child ID | Points |
|---|---|---|---|
| Day1 | 1 | A | 5 |
| Day1 | 1 | B | 10 |
| Day1 | 2 | C | 20 |
| Day2 | 1 | A | 10 |
| Day2 | 1 | B | 15 |
| Day2 | 2 | C | 30 |
I'm trying to create a view that would show me this:
| Date | Parent ID | Points |
|---|---|---|
| Day1 | 1 | 15 |
| Day1 | 2 | 20 |
| Day2 | 1 | 25 |
| Day2 | 2 | 30 |
I've tried summing with a GROUP BY but am currently only able to produce this:
| Date | Parent ID | Points |
|---|---|---|
| Day1 | 1 | 40 |
| Day1 | 2 | 50 |
| Day2 | 1 | 40 |
| Day2 | 2 | 50 |
The sum function seems to be summing all data used in the parent class regardless of date-related group-by statements. How would I make sure that the points are summed by day, rather than just totally?
CodePudding user response：
group by Date, ParentID seems to work fine:
select Date, ParentID, sum(Points) as Points
from table_name
group by Date, ParentID;
To create a view:
create view sumPoints as
select Date, ParentID, sum(Points) as Points
from table_name
group by Date, ParentID;