1.13.1 Example 1: Determine the number of purchases for each customer

Obtain from the sales history table (SALESLIST) a list of the number of purchases for each customer.

Table to search

Specification example

SELECT "USERID",COUNT(*)
    FROM "SALESLIST"
        GROUP BY "USERID"

Retrieval results

Note

The columns specified in the GROUP BY clause must match the columns specified between the SELECT statement and the FROM clause, or an SQL error results. In the example above, the USERID column is specified in both locations.

An example of an SQL statement that generates an error is given below.

Example of an SQL statement that generates an error

SELECT "USERID","PUR-CODE",COUNT(*)
    FROM "SALESLIST"
        GROUP BY "USERID"

Example of a correct SQL statement

SELECT "USERID","PUR-CODE",COUNT(*)
    FROM "SALESLIST"
        GROUP BY "USERID","PUR-CODE"

The SQL statement above obtains the number of purchases by customer (USERID) and product code (PUR-CODE). The retrieval results are as follows.

Retrieval results

To Page Top