Mysql – MAX primary and secondary?
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…
Postgresql – Inexact GROUP BY aggregation
I'm trying to figure out a way to reason about a fairly simple problem: A table foo has two columns, id with unique identifiers, and value with a random numeric value. Given a constant threshold value, find the groups of…
Postgresql – Join and group by based on a value of a column to filter
Let's say I have two tables with these data: author id | name --------- 1 | John 2 | Jack 3 | James 4 | Bob 5 | Gorge book id | author_id | title | state ------------------------------------- 1 |…
Postgresql – SQL- group by two columns, take average and count sample for each case
My table looks like below x y z a ab 2 a ab 4 a ac 4 b ab 6 b ab 4 b ad 8 c ac 10 c ac 10 What I am looking for is this: x…
MySQL – Use of Group BY clause
I am using the Sakila Database for practice. I am calculating - Sales by Store. It provides a list of total sales, broken down by store. It incorporates data from the city, country, payment, rental, inventory, store, address, and staff…
MySQL user events actions and sessions sequence
I have db on MySQL - dataset with users actions logs. We have next data collection logic - every user have it own user_id and we record log of event actions during user session. user_id session_id dateTime event 1 aa…
Laravel – groupby sum and count tickets
I am learning to use groupBy. In my exercise, I must count the number of tickets that were sold and add the total money collected. $tickets = Ingresostaquilla::groupBy('tipo') ->selectRaw('count(*) as quantity, tipo') ->get(); I did the count by type of…
Select multiple column in groupBy laravel 5.7
I am using Laravel 5.7 and i need to generate top selling products of current month for my reports. So i join two table products and sells. I want three columns in return which are sell_quantity, product_name*, and product_id. So…
Mysql – How to SQL select only most recent from related table
I have a table of requests from which I want to select everything, and I have a related table of positionReports where I want to select only the most recent report. The below SQL does what I want as long…
Postgresql – How can I make this aggregation query fast for my needs?
Describing the scenario I have this table called event defined in PostgresSQL that, among some not relevant ones, basically has 5 columns: id (integer): primary key of the table date (timestamp): date when the event occurred fk_type (integer): foreign key…