Sum with group by (with case statement) in MySQL query
I am facing one issue in my following Mysql query. I have a table with columns id, pid, state, price1, discount, tot4 and tech1. I try to get a group by sum where pid is same and state value is…
Postgresql – SQL – Group By values across columns
I have something like a netflow table and would like to group it in such a way that it groups it by (src_ip, src_port, dst_ip, dst_port) where the values could be swapped between the src and dst fields. src_ip src_port…
Laravel group by if column value exits other wise return records
Laravel group by if column value exits then group by perform other wise as it is return records in single laravel query i have tried this $sql = $this->expense->builder()->where('session_year_id',1)->whereNull('staff_id')->orWhereNotNull(function($q){ $q->groupBy('month'); })->get();
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…