Suppose I have a data like this I want to calculate the average of VALUE by Group ID, but conditional on VALUE being smaller or equal than the median by group. So for instance, for Group 1, the median is 4 and 2,3,4 are smaller or equal than 4, so it should give for group 1: Does anyone have a
Tag: oracle
How to select columns on the basis of column names in Oracle
I tried the following, but that doesn’t work. Answer In this example, I’m composing a select statement based on list of columns from a table (passed to function as a parameter) whose name satisfies certain condition. As it is Scott’s emp table and its columns don’t have any suffix, I&#…
Returning multiple identical rows in LISTAGG query
I’m using Oracle, and want to connect two tables into one select statement (for a report), and we’re required to concatenate some of the data from one table into a single row – for which I’m expecting to use LISTAGG. Simplified version of the problem: ACCOUNTS table: InitialDate Accoun…
SQL cumulative sum with mutilple conditions
I have a input table as this: I would like to calculate the cumulative sum of the flag for each, but the calculation should be refreshed if the flag is equal to 0 once again. So, the output table should be: So, any suggestion to solve this issue? I tried with this: But i have no idea how can I
Getting Incorrect results for selecting values between 2 dates
I am trying to run the below query to select some results between 2 dates but I am getting results as the condition doesn’t exist. So what is wrong here? Answer You are comparing dates with strings; the dates are converted to strings to do that comparison. From the documentation: During SELECT FROM oper…
Get the sum of rows using sql query
Suppose I have this table structure: I want to sum the quantity for the same name and last name and get the result as below Is it possible to get this result in a single SQL query ? it is easy to get the SUM with the GROUP BY but did not find a way to keep the Buy and
SORT dates that are not in same format – SQL
I have below table and I want to sort it by ascending order. Below command would work except not all of my dates are in correct format. Majority of them are in YYYY-MM-DD and few are in DD/MM/YYYY format. How would i sort them? Can i convert all of them in to on standard format and do the sorting? order
SQL join to find relavant codes and address
Below table contain address information. Code Code1 Code2 Address DAN001 DAN004 DAN005 ABC, Newyork. DAN004 DAN004 DAN004 Delhi, India DAN005 DAN005 DAN005 Ever, Belgium Please find table details below: Each Code have associated code1 and code2 and address last column. for example: DAN001 is associated with D…
Execution plan too expensive case when exists
I have the below query, but when I execute it runs forever. My execution plan shows a huge cost which I cannot locate. How could I optimize this query? I have tried some hints but none of them have worked 🙁 Kindly let me know if any additional information needed. Answer Use window functions: Not only is the q…
ORA-01722: invalid number – Everything seems a digit
I have the below query: However, when I execute it, the below error appears: But, when trying to find what was the entry in col2 that was causing issues, I get no results: Aby hints on how to keep searching for the col2 culprit will be hugely appreciated. Answer This expression is not going to find mixed digi…