MDBitz Development Resources

What is the GROUP BY Clause?

The GROUP BY clause of T-SQL is used to divide the results returned into groups.  This technique is often utilized to obtain aggregate information from the table as in the number of users you have from each state or number of books an author has written. In addition you can also use the group by command to return the results with related articles proceeding each other so that you can associate the data into sub objects.

The basic format for the GROUP BY clause is:

GROUP BY column1, column2, ... columnn

where it comes after the WHERE clause but before the ORDER BY clause in a SELECT statement.

Sample GROUP BY example

In this example lets assume we have the following two Tables: BOOKS and SALES

Using the above above tables lets do a query to find the total quantity sold for a book. To do this we are going to join the two tables based on the Book ID as well as grouping on that ID.

SELECT b.ID AS BOOK_ID, SUM(s.QUANTITY) AS NUM_SOLD
FROM BOOK b LEFT JOIN SALES s ON b.ID = s.BOOK_ID
GROUP BY b.ID

This will return the following results:

Utilizing the HAVING clause to restrict results

The HAVING clause is used to limit the groups that are returned from a query. In this command you can specify that the results must have a column equal to some value or that the sum or aggregate of columns of the group match some criteria.
The format for the HAVING command is:

GROUP BY column1, column2, ... HAVING criteria and|or criteria2 ..

Sample HAVING clause example

Utilizing the above database tables from the previous example we could add a HAVING clause to restrict the results to only return books that have sold more than 10 copies.

SELECT b.ID AS BOOK_ID, SUM(s.QUANTITY) AS NUM_SOLD
FROM BOOK b LEFT JOIN SALES s ON b.ID = s.BOOK_ID
GROUP BY b.ID HAVING SUM(s.QUANTITY) >=10

This query would have the following results:

Resources

• Sybase Manual – The Group By Clause
• Sybase Manual – Obtaining Aggregate Data

The DISTINCT command:

The DISTINCT command is used for retrieving unique data entries dependent on the columns specified. The opposite way of thinking about it is that it eliminates duplicate result rows. For a basic example lets assume we have the following person table and data

With the above example we could use the distinct command to obtain all the states that people in the table are from.

SELECT DISTINCT STATE FROM PERSON

This will return

where in not using the DISTINCT command would return New York three times. DISTINCT will work for all the columns that you are interacting with so if we modify the query to also return city in addition to the state the result would be:

When using the distinct command it is important to know that if you use the ORDER BY command or GROUP BY command that even if you don’t select the columns used in those commands in the final results the values in their columns will be considered when determining distinct rows. So in our previous example if we order by First Name then we will get 3 New York results as the First Name for those data rows are not the same.

Resources

• Sybase Manual – DISTINCT command

There are a few methods available to the Sybase Transact-SQL developer who needs to obtain the next primary key for the next row to be inserted. If the primary column is an identity column then the next_identity function can be used. The proper syntax is:

SELECT next_identity( "TABLE_NAME" )

If however you have not used the identity column then you can obtain the next ID to be used for INT type columns by getting the highest ID currenlty being used than adding 1 to it. This is done by using the isnull and MAX functions.

SELECT isnull( MAX(ID), 0 ) + 1 FROM TABLE_NAME

SYBASE Adaptive Server Enterprise (ASE) is a relational model database server, that uses Transact-SQL (T-SQL) as the procedural language for creating, modifying and querying the Databases.

Detailed below is a short overview of how to create tables in an ASE Database Server including auto generating ID SEQUENCES, foreign keys, and unique indexes.

Basic Format

Like other SQL implementations we utilize the create table command to create a new table in the Database:

CREATE TABLE EX_USER (
  ID INT not null,
  NAME VARCHAR(255) not null,
  ACTIVE TINYINT(1) not null,
  ROLE_ID INT not null,
  USER_NAME VARCHAR(25) not null,
  PASSWORD VARCHAR(25) not null
)

How this statement works is that you specify the table name after the create table statement and inside the () you define the various columns in the table with the format of name type  identity, null, or not null and optional other modifiers

Identity Columns

When created database tables we often want to create identity columns that are used to designate the row of data. In most cases we also want that column’s value to automatically populate on an insert with its value. In Transact-SQL this is done by the identity keyword. In our example EX_USER table lets modify the ID column to be an identity column by replacing not null with the identity keyword.

ID int identity

Unique Indices

In addition to identifiers to identify the data row in a table we also may want to specify a column to be unique in that every value is only allowed one at a time. This is accomplished by creating a unique constraint. This can be done as a separate statement or appended to the parameters in the create table statement.

CREATE TABLE EX_USER (
  ID INT IDENTITY,
  NAME VARCHAR(255) not null,
  ACTIVE TINYINT(1) not null,
  ROLE_ID INT not null,
  USER_NAME VARCHAR(25) not null,
  PASSWORD VARCHAR(25) not null,
  UNIQUE NONCLUSTERED (USER_NAME)
)

The nonclustered part of the statement means do not organize the data in order of the column. If by chance you wanted the data to order itself based on the column you can use the clustered keyword.

Foreign Keys

Foreign Keys are used to define columns whose value is from another table. This guarantees that the value found in the column is a valid value in the other column, and is used for lookup tables as well as for linking data across tables. In our example lets assume that we have a lookup table called EX_RULE that contains all the available user roles. We would have our RULE_ID column link to the table by the use of the references keyword.

RULE_ID INT not null REFERENCES EX_RULE(ID)

References

• Sybase’s Transact-SQL User’s Guide