The following data is present in my Color ActiveRecord model: id colored_things 1 [{“thing” : “cup”, “color”: “red”}, {“thing” : “car”, &…
SQL Get users with max occurrences in table
Given the following table transactions, which records the IDs of sellers and buyers who had a transaction, I would like to determine the user who was involved in the highest number of transactions and …
Add multiplicity as rows
I use Sparx EA to build a relationship database (MS SQL Server). The tool has a function to create charts. I use the query: SELECT system.Name AS Series, systemElement.Name AS GroupName FROM t_object …
Calculate exact month-difference between two dates
DB-Fiddle CREATE TABLE inventory ( id SERIAL PRIMARY KEY, inventory_date DATE, product_name VARCHAR(255), product_value VARCHAR(255) ); INSERT INTO inventory (inventory_date, …
Get next row with clause A since last row with clause B
I have the following table: I am looking for a way to get all customers whose status is currently not ‘ok’ with an indication since when the status is not ‘ok’. The result should be: My attempt is the following, but it gives incorrect values for customers that are currently ‘ok&#…
Delete Rows which are fetched by a select query joining several tables
I have written the following select: What I want to do: I want to delete the rows in reminder, which are fetched by the described select. What I tried: Result: 00907. 00000 – “missing right parenthesis” I wonder if i can basically delete these rows in this manner. I also tried ‘exists&…
How to remove “not a GROUP BY expression” error from the statement?
The schema of the table is: Emp (Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno) Question: Display the job, deptno and average salary of employees belonging to department 10 or 20 and their salary is more than 2000 and average salary is more than 2500. My Query: Error: ORA-00979: not a GROUP BY expressio…
Grouping the common values in Oracle
I have a table with sample values as below In this table, all the values in Col1 will have its supporting values in Col2. The values A1 and A2 are like master values and they will never appear in Col2. I need to make an output displaying this master values in a new column like below What would be the
Scope_identity is giving null values? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed last year. Improve this question I have stored procedure in which I use scope_identity() Error I get: cannot insert null into i…
Build a report with SQL with combining columns and rows?
Here is my table: Table Purchase Report PURC_ID CUS_ID Product QTY Date 1 John Leg_01 2 2021-04-09 2 John Head_01 1 2021-04-09 3 Dawn Head_01 1 2021-04-09 4 Dawn Shoulder_01 2 2021-04-09 5 Dawn Leg_01 1 2021-04-09 6 Keith Leg_01 2 2021-04-09 I would like to build the report as follow: Table 4: (PURC table wil…