I have the following table resulted from but now I want to remove the repeated rows except the Sum of ‘TotalBill’. Answer Use GROUP BY: Note that for the billing date, the two “duplicate” records you have highlighted have different dates. It is not clear which date, if any, you want to…
Tag: sql
Is this a perfect SQL query to fetch random items
I have this below query to fetch books of a random author and order by the books last modified date. Because I am new to SQL something tells me this query can be improved and simplified. I would appreciate the help of the SQL experts, if this is already simplified then I will consider myself an SQL expert :).…
Can I combine parts of multiple columns in Oracle SQL
I’m a student in college and I’m working on a project with Oracle DB. I was wondering if it was possible to make a column value appear as concatenated parts of other columns when I Insert a line and use that column as PK ? So on a table like this : So when I insert a line I’d like
ORA-00932: inconsistent datatypes: expected – got CLOB – while using clob in regexp_substr function
I am trying to use CLOB variable in regexp_substr function as below clob_variable is of type CLOB and contains ~ separated ids. While executing the update statement I am getting below error: ORA-00932: inconsistent datatypes: expected – got CLOB Can we use CLOB with RegExp? If not, is there any way to c…
MySQL join dataset on at least X items
My question in a SQL Fiddle. How can I join all elements in table A with all elements in table B via a many-to-many mapping table, even if no relationship exists? My LEFT JOIN solution isn’t giving the results I expect. Details: Given these tables: And this seed data: I need a report like this: The quer…
SQL Getting row number only when the value is different from all previous values
I want the count adding one only when the value has not been show before. The base table is: The goal is: I am thinking to compare the current row with all previous rows, but I have difficulty to call all previous rows. Thank you! Answer Several different ways to accomplish this. I guess you’ll get to p…
SQL pivot the column values
Problem Statement: Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. Note: Print NULL when there are no more names corresponding to…
Postgresql weird outcome after joining on dates with interval of 1 month
I have a table like this: The table indicates the date a user accessed a website and user_id is the user id. I wanted to check whether the user logged in a month and in the previous month. I wrote the next query: However, when I run this query table a shows the previous month and table b is the
Extract first argue from column with T-SQL
I need to extract the first argument: “PATHTOPACKAGE” from the string below: which is a column stored at SQL Server msdb.sysjobsteps.command Is there a way to do it by using Regex or Spliting it by blank spaces? Answer Based on the one example we have, a couple of CHARINDEXs a little bit of simple…
SQL: select the last values before a space in a string
I have a set of strings like this: I need to extract only the last values from the right and before the space, like: how will the select statement look like? I tried using the below statement, but it did not work. Answer Perhaps the simplest method in SQL Server is: This is perhaps not the most performant met…