Sorry for the vague title, I’m a little lost here. I have two simple aggregate SQL queries, but I struggle to combine them in a functional way (likely through a subquery) in IBM DB2. Main goal is to run COUNT function in INVOICES table – but with the starting date based on a subquery MAX aggregate…
Inner join table with multiple rows/results grab the first one . Error : #1055
I have the following query : Error: #1055 – Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘c.category_slug’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by One product can have…
JOOQ – inline Converter not being applied
In my build.gradle I use a converter in my forcedTypes. This works fine where i need it. forcedType { userType = ‘java.util.List’ converter =…
Joining two tables when one table has multiple corresponding values
For some background, what I have is an ordering system that populates a database. In the database there are two tables, order_Parent, order_Children. order_Parent contains broader information such as ID, shippingDesination, createdBy, highPriority. There are more columns but the ones that I am focused on are …
How to check if one of several fields is in the set?
I am generating the SQL queries from the C++ code. (sqllite3) I need to write efficiently the following request. How to optimize it: avoid pasting two times (V1, V2, V3, …), or take sets intersection of (T.field1, T.field2) and (V1, V2, V3, …), or create statement local variable (V1, V2, V3, ̷…
I have to get the total time spent for a question using SQL. I am unable to get the total time and provide the answer in minutes or H:M:S format
I am trying to find the total time taken per person and provide the answer in total minutes spent using SQL. The page_start_time is time data type.The data is shown below: This is the visitors table. I need to find the total time taken using the page_start_time field per USER_ID. I tried the following code: I…
Counts from many tables into one set of results
I have multiple tables all with unique product IDs, a user ID and a date. Is there any way to display a single table to show counts within each table split between each user ID. I would want one row …
Obtain latest record for a given second Postgres
I have data with millisecond precision timestamp. I want to only filter for the most recent timestamp within a given second. Ie. records (2020-07-13 5:05.38.009, event1), (2020-07-13 5:05.38.012, event2) should only retrieve the latter. I’ve tried the following: But then I’m asked to group by even…
single table parent child relationship query
I have written a query to get the items from the table which doesn’t have any child items. It’s working fine but is very slow. Any better/easier/optimized way to write the same thing? Few of the fields are these to get the idea of a structure Query should only return the 4rth item Answer Try below…
Sql Grouping insight Xml
I need to get different row data to 1 row based with id and need to group by its status. I tried in several ways and finally found a solution using xml, but when I use xml I don’t get the output I want. This is a sample code I used to try the logic. returned output: expected output: Answer