Applying mathematics directly to database table

When your company decides to reduce the prices of all products to 5% during this difficult economic time, what will you do to adjust the product prices in your database. Here is what I will show you. If you perform the query directly to production database, first, I would recommend you duplicate the product table in case you enter an incorrect query. I always do this when I run query to change data in database. Then write you query UPDATE tbl_products SET price = price –…

Continue reading »

Concatenate 2 or more fields from a database table

If you want to get a result of firstname and lastname together as a single field, you will use CONCAT_WS(separator string, field1, field2,…) function. Here’s I will show you how. For example, a table name Manager has 4 fields and some records as shown below. id firstname lastname email_address 1 Linda Doe linda@mynetworksolutions.com 2 Didi Menita didi@yahoo.com 3 Jane Doe jane@adobe.com 4 Elisa Bethoni bethoni@microsft.com You will use the following query statement to produce the expected result as shown below. SELECT id, CONCAT_WS(‘ ‘,firstname,lastname) AS…

Continue reading »

Mathematical Functions in MySQL

Name Description ABS() Return the absolute value ACOS() Return the arc cosine ASIN() Return the arc sine ATAN2(), ATAN() Return the arc tangent of the two arguments ATAN() Return the arc tangent CEIL() Return the smallest integer value not less than the argument CEILING() Return the smallest integer value not less than the argument CONV() Convert numbers between different number bases COS() Return the cosine COT() Return the cotangent CRC32()(v4.1.0) Compute a cyclic redundancy check value DEGREES() Convert radians to degrees EXP() Raise to the…

Continue reading »

Group By and Order By

In MySQL query, GROUP BY and ORDER BY will give you 2 different results. Group By will return the unique values of a field while Order By will return number of records with alphabetical or numeric order based on what field you specify. This is an example of query using Group By, you will see that the results will only list a unique lastname: SELECT firstname, lastname, email_address FROM somedb.users GROUP BY lastname; The results will be: Alice, Bloom, bloom@gmail.com Daniel, Nguyen, dnguyen@yahoo.com Lynda, Dina, dina@mail.com While the query using…

Continue reading »

Error related to only_full_group_by when executing a query in MySql

I upgraded my system from MySQL 5.4 to 5.7.17 with PHP for a web application I’m working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7.17 I get this error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx.tablenamehere’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by This is my query: So I tried to…

Continue reading »