Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I was trying to query a complicated sql query with joins, groups, etc and after upgrading mySQL the group by stopped working.

In order to fix issue I had to open mySQL and on the database I had to run the following query:

SET GLOBAL sql_mode = '';

Escaping single quote in SQL

Very simple, in order to escape a single quote in SQL, just add another single quote before your actual single quote as the following example:

Error:  UPDATE SampleTable SET (Title = ‘Welcome to Bob’s Burgers’)

Solution:  UPDATE SampleTable SET (Title = ‘Welcome to Bob”s Burgers’)

SQL Computed Values

Lets say I would like to query a table but I would like to compute a column according to another value.

Example:

Original Table Columns:

• Id (int)
• FirstName (varchar)
• LastName (varchar)
• Married (bit)
• FamilyName (varchar)

I would like to get from table “Females” the following data, where Last Name should have the original nee surname if married:

• Id
• FirstName
• LastName

Sql Statement: 

SELECT Id, First Name, (CASE WHEN Married = ‘1’ THEN LastName + ‘ Nee ‘ + FamilyName ELSE LastName END) As LastName FROM Females

 

Result would look like:

Id	First Name	Last Name
1	Jane	Smith
2	Sue	Duffy Nee Perry
3	Kelly	Johnson Nee Smith
4	Marie	Dickson

Where 1 and 4 have Married as False, while 2 and 3 have Married as True

 

 

 

 

LinQ to SQL simple Insert Update and Delete

Since I was given a task to implement new technologies, I came about selecting LinQ. It was quite hard to find a simple place or tutorial where I could find how to do simple queries without complexities. Therefore I decided to post a blog to describe as simple as can be the process of inserting updating and deleting from LinQ to SQL.