I have a dataset about chess games, and in the winner column there are only 2 values: ‘white’ and ‘black’. I want to show COUNT(winner) as WHITE_WINS and COUNT(winner) as BLACK_WINS. I tried inner joining the table by itself, however couldn’t get the correct SQL query to do the job. How can I get a table like: Answer Use conditional
Arranging data by year and proportion
I have one table with the following format: Note 1: [End_Date = NULL] means that participation has not ended. [Proportion = NULL] means that participation is 0. Output: I want to have the proportion of each sub_entity for every active year. Note 2: One Sub_Entity can be a proportion of different Entities. The final table will be something like: Note
Teradata SQL getting last not null value
I have a table in Teradata that looks like this: Account_Num Install_Due_Dt Install_Num Install_Pay_Dt 12805196 12/08/2019 1 12/08/2019 12805196 10/09/2019 2 …
High performance query to get datetime when value was last changed
The data I am working with Consider the following 2 database tables: CREATE TABLE [dbo].[Contact]( [ID] [int] IDENTITY(1,1) NOT NULL, [Contact_UID] [uniqueidentifier] NOT NULL CONSTRAINT […
IntelliJ IDEA does not recognise specific database schema in Javascript/Typescript files
At some point I was amazed when I managed to set the support of specific schema in my nodejs modules. Unfortunately, later IntelliJ IDEA decided to crash and I needed to remove my .idea folder, thus I lost all of my configurations. So, the problem is that when I write SQL queries it does not find tables or functions which
System.InvalidCastException: When trying to add an object to a SQL database using Entity Framework in C#
I am trying to use Entity Framework in C# to add objects to a SQL database. The data is coming from a combination of textboxes and datepicker controls. I’m guessing this is a problem with how I’m …
Trim unwanted text with start and end index in column
I have a table in a MySQL Server 5.7 database. This table has a varchar(256) column called ‘url’. Unfortunately, The values in this field have a path included and not to be included when importing …
Subtract one hour result
I am pulling live data from a db2 server, I am building a Power bi report that shows by time each transaction made. There is a time difference between our server location so the result is one hour …
SQL extract total number of instances of a substring in an array in JSON fields
How can I get a SQL Query to go within the JSON of a Column, and find the number of instances of a substring within that entire Column? I am using SQL Server 2016. i.e FruitPageTable [Id, PageData (…
SQL Server – restart running count on change
The schema Simplified, a sales table with non-unique sales IDs. Within each ID there will be a sale of an element S, which can be followed by a sale of up to two elements L linked to the element S. …