I am trying to write a query to track the changes of a boolean column in a table. The table looks like this So what i would want is row 3 where the is_active changed to false. Then after that i would want the next row where it changed to true again. This is what i tried: So the subquery
Tag: mysql
MySQL Debit/Credit cumulative balance
I would like a cumulative DR or CR balance for the following table: For the above table, a cumulative Balance column would be: Decreasing a DR below 0 makes it a CR Decreasing a CR below 0 makes it a DR Where balance is 0, value is 0 DR It could be calculated as CR’s being a negative value, but
SQL Date range by month returns in multiple rows
I am looking to get the count of entries by the month. However, this is returning the total count. I would like it to be something like. Month —– Count January —- 500 February —- 600 If I can’t get the month in there, thats fine, but I would definitely like to have it so the coun…
Can somebody help to translate this MySql query to JPQL or HQL?
Don’t forget to check the image below Objects: 1) Question 2) Tags The relationship is ManyToMany The MySql query is following: Need to translate to JPQL or HQL The main goal is to get list of the most frequency tags like:enter image description here Answer Try: The given JPA query is translated to SQL …
Compare two values in a row
select event_slots.id as event_slot_id, u.first_name, u.id AS user_id, sum(case when next_round = 1 then 1 else 0 end) AS yes, sum(case when next_round = 2 then 1 else 0 end) AS no from event_slots …
MySQL-Statement. How do i skip rows in query where all rows equal null
I have created a query to construct a table with the following code: SELECT Date, SUM(CASE WHEN UniqueId = ‘NonComLong’ THEN Value ELSE NULL END) AS ‘NonComLong’, SUM(CASE WHEN UniqueId = ‘…
MySql Trigger Function about status
I would like to ask a solution for mysql database. since i made database(order) about 3 column named as order_date(datatype – date),expired_date(datatype – date) and status(varchar(10)). as status …
Trying to JOIN columns from two different tables
I have one table (city_data) with the columns ‘year’, ‘city_name’ and ‘avg_temp’. The other table is global_data and has the columns ‘avg_temp’ and ‘year’. I am trying to create a table which has the …
how to select all the values in hive with distinct of 2 columns in hive
I have a hive table that looks like this (total 460 columns) colA colB ……. ce_id filename ……… dt v j 4 gg 40 v j 5 gg …
Country-wise Analysis of Movies
Write a query to find the number of movies rented across each country. Display only those countries where at least one movie was rented. Arrange these countries in the alphabetical order. DB: https://dev.mysql.com/doc/sakila/en/sakila-structure.html Below is the written code my output is displayed as below Ho…