Mysql – creating new table by conditioning on columns of existing table
I am a new sql learner. I am stuck with the following problem: Suppose I have the following table : ID detail A1 11 A1 12 A1 null A1 0 A2 11 A2 13 Now I want to create a…
MySql group by is slow
I have a scenario whereby I am trying to fetch a user's most recent messages from over 2 million rows and group them by their parent (or thread) id. However, this grouping is causing the query time to be around…
Postgresql – How to optimize a count/group by SQL query that is too slow
I'm currently developing a SOAP service with several operations. Requests and responses are saved in a table called "message". Each Request and each Response is its own entry in the table and each Request/Response pair has a corresponding correlation_id. I…
MySQL Derived column not recognized as functionally dependent
With the following table: CREATE TABLE `Example` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `properties` json DEFAULT NULL, `hash` binary(20) GENERATED ALWAYS AS (unhex(sha(`properties`))) STORED, PRIMARY KEY (`id`), KEY `hash` (`hash`) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; the column hash…
Convert panda dataframe into json object with groupby
Basically I have a dataframe that looks like this num platform region id 569094 10 AMZN in 569094 5 AMZN ca 569094 12 NFLX us 385687 15 DIS in 385687 20 NFLX ca I need to convert this into json…
Mysql – How to go through each of the entry for a particular ID in the table and classify the IDs?
I would like to go through all the IDs and classify each ID as Car only, Bike only, both, and ignore anything with truck. In the end I would like to see only one ID with specific Product_category. Structure of…
MySQL MIN/MAX Select based on different criteria in a single select statement
In mySQL, have a large table of history data and I am looking for an elegant way to be able to select the earliest/latest event types in a single select statement without grouping by Item and Event type and running…
Postgresql – Calculate time duration while iterating on the same column
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…
Json – How to group array entries by key and by key value in jq?
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":…
Postgresql – Getting error on selecting those columns also which are not used to create group while using using group by?
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…