It can be done by using GROUP BY clause in the SELECT statement. We can specify a column as grouping criteria with the help of GROUP BY clause. Due to the specified grouping criteria, rows with the same value in a particular column are considered as a single group. In this way, the result set returned by MySQL SELECT statement will be divided into groups.ExampleFollowing is a good example to understand it −We have a table named ‘employees’ as follows −mysql> Select * from employees; +------+-------------+--------+------------+ | id   | designation | Salary | DoJ        | +------+-------------+--------+------------+ ... Read More

As we know that GROUP BY clause in a SELECT statement can divide the result set, returned by MySQL, in groups. Now if we want to return only some specific groups then need to apply filtering criteria at the group level. It can be done by using HAVING clause inside the GROUP BY clause. The example below will demonstrate it −ExampleSuppose we want to return only the group which is having an average salary of 55000 then we need to use filtering criteria as follows in HAVING clause −mysql> Select count(*), AVG(salary), Designation from employees GROUP BY designation having AVG(salary) ... Read More

We can use COUNT(*) and GROUP BY clause to find out the repetition of a value in the column. Following is the example, using COUNT(*) and GROUP BY clause on ‘Name’ column of table ‘Student’, to demonstrate it −mysql> select count(*), name from student group by name; +----------+---------+ | count(*) | name    | +----------+---------+ | 1        | Aarav   | | 2        | Gaurav  | | 1        | Harshit | +----------+---------+ 3 rows in set (0.00 sec)The result set of above query shows that which value is repeated ... Read More

It is because without GROUP BY clause the output returned by MySQL can mislead. We are giving following example on the ‘Student’ table given below, to demonstrate it −mysql> Select * from Student; +------+---------+---------+-----------+ | Id   | Name    | Address | Subject   | +------+---------+---------+-----------+ | 1    | Gaurav  | Delhi   | Computers | | 2    | Aarav   | Mumbai  | History   | | 15   | Harshit | Delhi   | Commerce  | | 20   | Gaurav  | Jaipur  | Computers | +------+---------+---------+-----------+ 4 rows in set (0.00 sec) mysql> ... Read More

When we pass an out-of-range value in UNIX_TIMESTAMP, MySQL returns 0. The valid range of value is same as for the TIMESTAMP data type.Examplemysql> Select UNIX_TIMESTAMP('1969-01-01 04:05:45'); +---------------------------------------+ | UNIX_TIMESTAMP('1969-01-01 04:05:45') | +---------------------------------------+ |                         0             | +---------------------------------------+ 1 row in set (0.00 sec)When we pass an out-of-range value in FROM_UNIXTIME, MySQL returns NULL. The valid range of values is same as for the INTEGER data type.Examplemysql> Select FROM_UNIXTIME(2147483648); +---------------------------+ | FROM_UNIXTIME(2147483648) | +---------------------------+ | NULL                      | +---------------------------+ 1 row in set (0.00 sec)

We can use FROM_UNIXTIME() function to retrieve the value, as MySQL TIMESTAMP, stored as INT in the column of a table.For example, we have a table called ‘test123’ which has a column named ‘val1’. In this column, we stored the integer values as follows −mysql> Select * from test123; +------------+ | val1       | +------------+ |     150862 | | 1508622563 | |  622556879 | | 2147483647 | +------------+ 4 rows in set (0.00 sec)Now with the help of the FROM_UNIXTIME() function, we can retrieve the column integer values in the form of MySQL TIMESTAMP data.mysql> Select ... Read More

As we know that the value of timestamp can be converted to a number of seconds with the help of UNIX_TIMESTAMP() function. MySQL would ignore the microseconds added to the value of timestamp because the value of UNIX_TIMESTAMP is only 10digits long.Examplemysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336              | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36.200000')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336              | +-------------------------+ ... Read More

In MySQL, UNIX timestamp format is the way to represent time value as an integer. The integer value represented for the date value would be the number of seconds. The starting date for counting these number of seconds is ‘1970-01-01’.mysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ |              1508625336 | +-------------------------+ 1 row in set (0.00 sec)The UNIX_TIMESTAMP value is 10 digits long.

We have to use GROUP BY clause if we want to use group functions with non-group fields in the SELECT query. The general syntax can be as followsSyntaxSELECT group_function1, …, non-group-column1, … from table_name GROUP BY column_name;Examplemysql> Select COUNT(*), id from Student GROUP BY id; +----------+------+ | COUNT(*) | id   | +----------+------+ | 1        | 1    | | 1        | 2    | | 1        | 15   | | 1        | 17   | | 1        | 20   | +----------+------+ 5 ... Read More

In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.Examplemysql> Select UNIX_TIMESTAMP('2017-09-25 02:05:45') AS 'UNIXTIMESTAMP VALUE'; +---------------------+ | UNIXTIMESTAMP VALUE | +---------------------+ | 1506285345          | +---------------------+ 1 row in set (0.00 sec)The query above shows that UNIX TIMESTAMPS values are stored as 32 bit integers whose range is same as MySQL INTEGER data type range.mysql> Select FROM_UNIXTIME(1506283345) AS 'MySQLTIMESTAMP VALUE'; +----------------------+ | MySQLTIMESTAMP VALUE | +----------------------+ | 2017-09-25 01:32:25  | +----------------------+ 1 row in set (0.00 sec)The query ... Read More