Wednesday, January 12, 2022

Calculate different types of averages using SQL

calculating averages with sql

As we learned at school there are three types of averages:

  1. Mean:  Which is what people when they use the word "average" mean (pun is intended). It is calculated by adding several values together and dividing them by the total number of values.
  2. Median:  The midpoint of a sequence of numbers. This number may or may not be a value in the sequence.
  3. Mode: The most frequent number of the set of data.

Fortunately Db2 for i gives us scalar functions for the first two, and we can easily determine the third.

Let me start with my list of values, that are all taken from field FLD1 in the file TESTFILE.

01  SELECT * 
02    FROM TESTFILE 
03   ORDER BY FLD1

Wednesday, January 5, 2022

Retrieving the file and library name from a SQL alias

get name of file and library alias was built over

I use SQL aliases a lot of the time to access data from a file that contains multiple members. When an alias has an undescriptive name like ALIAS1 which file was it built over?

Fortunately there are two scalar functions that allow me to retrieve that information from the alias:

  • TABLE_NAME:  Name of the file that the alias is built over
  • TABLE_SCHEMA:  The library in which file that the alias is built over is found

Before I get to use those scalar functions I need a file with multiple members and aliases built over those members.

Let me start with the file. You will not be surprised to find that it is called TESTFILE.

01 A          R TESTFILER
02 A            FLD1         100A         VARLEN

I use the following command, CRTPF, to create my physical file.

Saturday, January 1, 2022

Happy New Year, 2022!

Last year was blighted by the COVID pandemic again. I am sure all of us are glad to see another year dominated by the virus behind us. I am keeping my fingers crossed that we might start returning to a more "normal" life this year.

Despite all the restrictions upon our working lives IBM still managed to bring us a new Power server series, Power 10, starting with the E1080 server. As well as two Technology Refreshes. You can read about it all on this website.

Every year I look to see what were the most read posts of the year. These were: