Let's say there is a table structured like this:
ID | article_id | article_count | created_at
---|------------------------------------------
1 | 1 | 10 | 2019-03-20T18:20:03.685059Z
2 | 1 | 22 | 2019-03-20T19:20:03.685059Z
3 | 2 | 32 | 2019-03-20T18:20:03.685059Z
4 | 2 | 20 | 2019-03-20T19:20:03.685059Z
5 | 1 | 3 | 2019-03-21T18:20:03.685059Z
6 | 1 | 15 | 2019-03-21T19:20:03.685059Z
7 | 2 | 3 | 2019-03-21T18:20:03.685059Z
8 | 2 | 30 | 2019-03-21T19:20:03.685059Z
The goal now is to sum over all article_count of all article_ids for the last entries per day and give back this total count per day. So in the case above I'd like to get a result showing:
total | date
--------|------------
42 | 2019-03-20
45 | 2019-03-21
So far, I tried something like:
SELECT SUM(article_count), DATE_TRUNC('day', created_at)
FROM myTable
WHERE created_at IN
(
SELECT DISTINCT ON (a.created_at::date, article_id::int) created_at
FROM myTable a
ORDER BY created_at::date DESC, article_id, created_at DESC
)
GROUP BY DATE_TRUNC('day', created_at)
In the distinct query I tried to pull only the latest entries per day per article_id and then match the created_at to sum up all the article_count values. This does not work - it still outputs the sum of the whole day instead of sum up over the last entries.
Besides that I am quite sure that there might be a more elegant way than the where condition.
Thanks in advance (as well for any explanation).
JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)
