|=--------------------------------------------------=|
      _________________  .____     ___________________
     /   _____/\_____  \ |    |    \_   _____/\______ \
     \_____  \  /  / \  \|    |     |    __)_  |    |  \
     /        \/   \_/.  \    |___  |        \ |    `   \
    /_______  /\_____\ \_/_______ \/_______  //_______  /
            \/        \__>       \/        \/         \/
    
    
    |=--------------------------------------------------=|
    . |03.06 - Querying the data III.                    .
    |=--------------------------------------------------=|
    
      The  power   of  SQL  is  in   sorting,  filtering,
    subseting  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.
    
    
    |=--------------------------------------------------=|
    |=--------------------------------------------------=|