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