MySQL FAQs - SQL SELECT Query Statements with GROUP BY

Home >> FAQs/Tutorials >> MySQL Tutorials

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

Can SELECT Statements Be Used on Views?

Select (query) statements can be used on views in the same way as tables. The following tutorial exercise helps you creating a view and running a query statement on the view:

mysql> CREATE VIEW myLinks AS SELECT * FROM fyi_links 
   WHERE url LIKE '%fyi%';
Query OK, 0 rows affected (0.49 sec)

mysql> SELECT tag, counts, url, DATE(created) 
   FROM myLinks ORDER BY counts DESC;
+------+--------+-------------------+---------------+
| tag  | counts | url               | DATE(created) |
+------+--------+-------------------+---------------+
| SQA  |      6 | sqa.fyicenter.com | 2006-07-01    |
| DEV  |      4 | dev.fyicenter.com | 2006-04-30    |
| DBA  |      3 | dba.fyicenter.com | 2006-07-01    |
+------+--------+-------------------+---------------+
3 rows in set (0.00 sec)

How To Filter Out Duplications in the Returning Rows?

If there are duplications in the returning rows, and you want to remove the duplications, you can use the keyword DISTINCT in the SELECT clause. The DISTINCT applies to the combination of all data fields specified in the SELECT clause. The tutorial exercise below shows you how DISTINCT works:

mysql> CREATE TABLE fyi_team (first_name VARCHAR(8), 
   last_name VARCHAR(8));

mysql> INSERT INTO fyi_team VALUES ('John', 'Gate');
mysql> INSERT INTO fyi_team VALUES ('John', 'Russell');
mysql> INSERT INTO fyi_team VALUES ('John', 'Seo');
mysql> INSERT INTO fyi_team VALUES ('John', 'Gate');
mysql> INSERT INTO fyi_team VALUES ('James', 'Gate');
mysql> INSERT INTO fyi_team VALUES ('Peter', 'Gate');
mysql> INSERT INTO fyi_team VALUES ('John', 'Gate');

mysql> SELECT * FROM fyi_team;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Gate      |
| John       | Russell   |
| John       | Seo       |
| John       | Gate      |
| James      | Gate      |
| Peter      | Gate      |
| John       | Gate      |
| John       | Gate      |
+------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT DISTINCT * FROM fyi_team;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Gate      |
| John       | Russell   |
| John       | Seo       |
| James      | Gate      |
| Peter      | Gate      |
+------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT last_name FROM fyi_team;
+-----------+
| last_name |
+-----------+
| Gate      |
| Russell   |
| Seo       |
+-----------+
3 rows in set (0.04 sec)

What Are Group Functions?

Group functions are functions applied to a group of rows. Examples of group functions are:

COUNT(*) - Returns the number of rows in the group.
MIN(exp) - Returns the minimum value of the expression evaluated on each row of the group.
MAX(exp) - Returns the maximum value of the expression evaluated on each row of the group.
AVG(exp) - Returns the average value of the expression evaluated on each row of the group.

(Continued on next part...)

Part: 1 2 3 4 5 6

MySQL Tutorials:

More...