Counting in SQL

The example below is an SQL function in Net.Data showing the simplest form of a count.


%{************************************  %}
%{********  Count actions ************  %}
%{************************************  %}

%FUNCTION(DTW_SQL) Count(OUT size) {
 Select count(*)
 From $(tableName)
 Where STDSTS >= $(posTo)

   %message{ 100: "" : continue %}

 %report {
  %row {
  @DTW_ASSIGN(size, V1)
  %}
 %}
%}


Embedded in RPG a count might look like the sample below, where the count is inserted into a program variable named COUNTER. In this example it appears that this is used to determine if a particular control number is on file, since the fetch has been qualified to return only one row.


           Exec SQL
              Select count(*) into :counter
              From VRHSTMTS
              Where shcntrl = :controlNumber
                    Fetch first row only;

Assume that a different type of count is required, not a row count, but a report of every other row, or in the example below, every 5th row of a table. Assume there is a table containing all of the districts in the state of Texas (there are a few), and there is a requirement to sample the data and report every 5th district.

SELECT 
   EXPR2.*
FROM 
   (SELECT Name, RowNumber,(EXPR1.RowNumber % 5) AS ROW_MOD
   FROM (SELECT Name, ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber'
      FROM 
         Districts 
      WHERE 
         state = 'TX' 
      ) EXPR1
   ) EXPR2
WHERE 
   EXPR2.ROW_MOD = 0