sorry for terrible description but I have struggled to word this My 2 tables are like this tblParent.. OrderID Customer 789 Bob tblChild OrderID SortOrder Price 789 1 20.00 …
Tag: group-by
Laravel : convert grouped data to one row
Hello please i want to convert grouped data with duplicated keys and different values to one row. For example : “book1” { { “id” : “1”, “group” : “book1”, “name” : “…
Check for condition in GROUP BY?
Take this example data: I need an SQL statement that will group all the data but bring back the current status. So for ID 1 the group by needs a condition that only returns the Completed row and also returned the pending rows for ID 2 and 3. I am not 100% how to write in the condition for this.
Pivot row values matching a pattern into column names
I have data that look like this: I would like to pivot these data so that for each set of rows with the same OH_IDNR and with the same common/non-unique part of OCV_VNAME, these columns are returned: OH_IDNR The common part of OCV_VNAME (e.g., ‘response_part_0_script_0_’) – n. One column for each …
How to get weekending having all approved records?
I have a table like below now want to get weekending ‘3/1/2020’ since it have all 1’s in isApproved like this I want to get list of weekending having all 1’s in isApproved Answer You can use aggregation and filter with a having clause: This works because isapproved only has 0/1 values.…
How can I get values of the Rating column together with their definition for every specific month by using Pivot with Month in MSSQL
Example : Data: Query: I get this error: Incorrect syntax near ‘,’. In line with ” Definition, Rating ” Result should look like this: Is this possible? Any help, please! Thank you! *The code above was based on PIVOT with MONTH(). Answer You could just use conditional aggregation: This …
Using window function in redshift to aggregate conditionally
I have a table with following data: Link to test data: http://sqlfiddle.com/#!15/dce01/1/0 I want to aggregate the items column (using listagg) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes 0 again for a group. i.e. for group g1, the…
Select data from specific row of grouped and joined table in MySQL
I have two tables, customers and orders which are inner joined. A customer can have several orders associated with them. In my selection, I then group by customers.id. I need to select the most recent …
SQL: Efficient way to count and group results by like value
I have a table that looks like this: What is the most efficient way to query it and return the following ? I was thinking to use case when statements but it seems messy. Answer In Presto you can split the delimited list into an array, then unnest the array. This gives you one record per element in each list.
Grouped conditional sum in Oracle SQL
my_table shows the account balance of each person’s credits N months ago. From this table, I want to get the monthly sum of each person’s balances for the past 2 and 3 months and divide each sum by 2 …