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 = '';

Advertisements

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.

Continue reading