Would it be possibile to calculate the daily sum of hours based on the difference between time present in same colums? date time 2017-11-08 08:25:57 2017-11-08 12:31:59 2017-11-08 13:28:42 2017-11-08 17:34:13 2017-11-09 08:28:08 2017-11-09 12:31:15 2017-11-09 13:20:13 I can get…
Given this json: { "hits": [ { "country": "PT", "level": "H2", "id": "id1" }, { "country": "PT", "level": "H1", "id": "id2" }, { "country": "CZ", "level": "H2", "id": "id3" }, { "country": "IT", "level": "H2", "id": "id4" }, { "country":…
I am using PostgreSQL If I want to get customer.first_name, customer.last_name or any other column in addition to customer_id and sum(total), I am getting error as shown below: select i.customer_id, cu.first_name, cu.last_name, sum(total) as amount from invoice i join customer…
I need to write a Query that gives me January 31's rolling 3 day average of total transaction amount processed per day I used the query below, but I'm having problem to retrieve information, because it keeps giving this error:…
I am still at beginner level on SQL programming so please bear in mind ... My database is similar to the below: +---------------------+------------+--------+------+--+ | Date | FileName | Type | File | | +---------------------+------------+--------+------+--+ | 2023-06-15 08:03:04 | InputParam |…
I have json object as follows {"resources":[{"srno":"1","name":"name1","projects":[{"prjname":"abc"}]},{"srno":"1","name":"name1","projects":[{"prjname":"def"}]},{"srno":"2","name":"name2","projects":[{"prjname":"abc"}]},{"srno":"4","name":"name4","projects":[{"prjname":"prq"}]},{"srno":"4","name":"name4","projects":[{"prjname":"stu"}]},{"srno":"4","name":"name4","projects":[{"prjname":"uvw"}]},{"srno":"5","name":"name5"}]} How can I get resources grouped by id,name - each of that should contain list of projects? basically,flattened to structured i.e. {"resources":[{"srno":"1","name":"name1","projects":[{"prjname":"abc"},{"prjname":"def"}]},{"srno":"2","name":"name2","projects":[{"prjname":"abc"}]},{"srno":"4","name":"name4","projects":[{"prjname":"prq"},{"prjname":"stu"},{"prjname":"uvw"}]},{"srno":"5","name":"name5"}]} can someone share jsonata expression to create same
Trying to select earliest order_date for each customer_id. Using following query SELECT customer_id, order_date FROM Delivery GROUP BY customer_id HAVING MIN(order_date) = order_date But those customer_id with more than one order_date rows are not showing in the result. For example…
I want to get data from two tables and group them by date, like a daily report that shows data from two tables. Example output: Date Active User Total Deposit 2023-07-31 10 12500 2023-08-01 5 10000 2023-08-02 20 20000 2023-08-03…
I have a MySQL table containing users, files and file ages as shown below. Users Files File age A 1 42 A 2 17 A 3 4 B 4 85 B 5 73 B 6 11 C 7 6 C…
With the below data example I want to group by the groupcol and within the group order by the val col and if no val exists secondary order by the ID. Understand the first MAX value (sql below) but how…