Find source and final destination using sql – Mysql
I have a table source_flight_destination like this ID Flight source destination 1 Indigo II BB 2 Air Asia AA II 3 Indigo BB JJ 4 spice jet SS BB 5 Indigo JJ SS 6 Air Asia II KK 7 spice…
Postgresql query: update status of limit number of records based on group size
I have a postgresql table contains a list of email addresses. The table has three columns, Email, EmailServer (e.g., gmail.com, outlook.com, msn.com, and yahoo.com.ca etc.), and Valid (boolean). Now, I want to group those emails by EmailServer and then update…
Check for all null values in a GROUP BY – Mysql
I have the following structre id val 1 ... . . 2 ... . . 3 null 3 null 3 null 4 ... . . Basically each id has multiple no. of values. And an id has either all values…
Calculating consecutive occurences in MySQL
I have a quick question in relation to windowing in MySQL SELECT Client, User, Date, Flag, lag(Date) over (partition by Client,User order by Date asc) as last_date, lag(Flag) over (partition by Client,User order by Date asc) as last_flag, case when…
SQL – how to get 0 in SQL as counting result? – Mysql
I created SQL query which should count the number of e-mails sent after 20.12.2022 for each user_id from ACTIONQUEUE table. My query counts everything correctly for all users, but it doesn't returns 0 in the table for the users with…
Groupby using Django's ORM to get a dictionary of lists from the queryset of model with foreignkey – Postgresql
I have two models, Business and Employee: from django.db import models class Business(models.Model): name = models.CharField(max_length=150) # ... class Employee(models.Model): business = models.ForeignKey( Business, related_name="employees", on_delete=models.CASCADE, ) name = models.CharField(max_length=150) # ... Here's a sample data: Business.objects.create(name="first company") Business.objects.create(name="second company")…
Spring Data, JPQL, Group by month of a date column(type LocalDate) – Postgresql
I have to get total price in month from a date yy-mm-dd with jpql query but I can't do it. @Query(value = "select new com.talan.food.dto.MonthIncomes( function('date_format',date,'%Y-%m'),SUM(p.price)) from Reservation p group by function('date_format',p.date,'%Y-%m')" ) public List<MonthIncomes> getIncomeByMonth(); And in the table…
Counting total of matching rows without filtering them with join table – Postgresql
I have the following structure: Training Type Contains multiple sessions Each session can have multiple slots A slot has a start date. (date column) How to compute for each Training Type the total of sessions whose all the slots are…
How to ignore implicit order by in psql group by query? – Postgresql
Postgres implicitly sorts row based on the group by column. In MySQL, we can change this behavior by setting the ORDER BY NULL. Is there any similar alternative way to achieve this in Postgres [Reference]?
How can I make a PostgreSQL query faster so that it gives back the lowest values?
I have the following data: id1 id2 score 281 33453 23 281 33452 23 281 36466 24 282 12118 14 282 27603 18 How do I write my query in PostgreSQL in the most efficient way so that I keep…