Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2 - Page 7

By Selena Sol

Distinct

It will often be the case that several rows will contain duplicate data in a specified column and that you will only want to view one to represent the group. For example, suppose you wanted to find out which employees had made sales in the SALES table.

If you were to use:

SELECT E_NUM
FROM SALES;

you would get:

E_NUM
-----
  101
  102
  101
-----

In this case, Employee number 101 has been reported twice even though once is enough. In this case, you would use the DISTINCT operator to filter out duplicates.

SELECT DISTINCT E_NUM
FROM SALES;

You would get:

E_NUM
-----
  101
  102
-----

Another common usage of distinct is to use it with a COUNT such as:

SELECT num_successful_salesmen  = 
   COUNT (DISTINCT E_NUM)
FROM SALES;

which would produce:

num_successful_salesmen
------
     2
------

Averages

A final tool that you can use to get aggregate data about a table is the AVG operator. The AVG operator follows the expected syntax of:

SELECT AVG(column_name)
FROM table_name
WHERE optional_where_clause;

For example, to determine the average salary of employees from the EMPLOYEES table, you would use:

SELECT AVG(EMP_SALARY)
FROM EMPLOYEES;

That SQL code would yield:

AVG (SALARY)
------------
       90000
------------


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date