|=--------------------------------------------------=|
  _________________  .____     ___________________
 /   _____/\_____  \ |    |    \_   _____/\______ \
 \_____  \  /  / \  \|    |     |    __)_  |    |  \
 /        \/   \_/.  \    |___  |        \ |    `   \
/_______  /\_____\ \_/_______ \/_______  //_______  /
        \/        \__>       \/        \/         \/


|=--------------------------------------------------=|
. |03.06 - Querying the data III.                    .
|=--------------------------------------------------=|

  The  power   of  SQL  is  in   sorting,  filtering,
subsetting  and grouping  data. In  the part  we will
examine the possibility of grouping data into groups.

  Let's assume we  would like to count  the number of
rows in a  table. We can use  grouping function count
like this:

  select count(*) from users;

  The above query gives us following result:

    count(*)
  ----------
           4
  
  That's number  of rows in  the table. Like  that we
can calculate  average numeric representation  of the
user_key.

  select avg(user_key) from users;

  The result of this query will be:

    avg(user_key)
  ---------------
              2.5

  We can give a resulting  column an alias and we can
query the maximum number in the user_key column. This
can be achieved like this:

  select max(user_key) as xuk from users;

  With result:

    xuk
  -----
      4

  The  grouping  can  be   of  course  combined  with
filtering   or  combined   into  multiple   resulting
columns. So we can use query as follows:

  select
    max(user_key) xuk,
    min(user_key) muk,
    avg(user_key) auk,
    sum(user_key) suk,
    count(user_key) cuk
  from users;

  So we get basic properties of the user_key column:

    xuk    muk    auk    suk    cuk
  -----  -----  -----  -----  -----
      4      1    2.5     10      4

  Just  a  short  note.   Average  is  in  this  case
calculated as:  (1+2+3+4)/4=2.5 Just like sum  of the
values divided the number of rows in the query.

  Above  that one  can group  the rows  in windowing.
function That would look like this                  .

  Let's  examine   following  query.  It   uses  CASE
expression to  divide the rows into  groups with rows
between 0 and 2 and  the rest. Then it uses windowing
functions to operate  on subgroup of the  rows in the
user_key ascending order.

  select
    user_key,
    grp,
    max(user_key) over (
      partition by grp
      order by user_key asc) wxuk,
    min(user_key) over (
      partition by grp
      order by user_key asc) wmuk,
    avg(user_key) over (
      partition by grp
      order by user_key asc) wauk,
    sum(user_key) over (
      partition by grp
      order by user_key asc) wsuk
  from (
    select
      user_key,
      case
        when user_key between 0 and 2
        then 1
        else 0
      end grp
    from users
  ) order by user_key asc;
  
  This query result is following:

  user_key    grp    wxuk    wmuk    wauk    wsuk
----------  -----  ------  ------  ------  ------
         1      1       1       1     1         1
         2      1       2       1     1.5       3
         3      0       3       3     3         3
         4      0       4       3     3.5       7

  That's it for this part on query grouping clause.


|=--------------------------------------------------=|
; v  | Thanks Linux and Vim | buy me a coffee   | o |;
|=--------------------------------------------------=|