Introducing CA Dataquery Query Creation › Query Creation Summaries › SQL COUNT Query

SQL COUNT Query

If all you need is a count of something, use the COUNT(*) function of the SELECT clause. The COUNT(*) function lets you tally the number of rows in the result table created by the query. Using COUNT (DISTINCT column-name) gives you the number of distinct values in a specific column. CA Dataquery executes the query and returns the results as output. A simple query with a COUNT function looks like this:

 SELECT COUNT(*)
  FROM CA-SLSHST-TBL

A more complex query with a COUNT function looks like this:

 SELECT COUNT(DISTINCT ITM-ID)
  FROM INVENTORY
   WHERE ON-HAND > 50 AND UNIT-PRICE >= 100.00

By adding a WHERE clause, say you want to know how many different items are in inventory where the number on hand exceeds 50 and the unit price is equal to or greater than $100.00.

Similar results can be obtained by using other functions in a SELECT clause. You can obtain total, average, minimum, and maximum amounts.