17. SQL functions and summaries

There are a whole load of functions that we can run. Let’s look at what happens when we run all of these functions:

SELECT * from genes;
SELECT COUNT(*) FROM genes;
SELECT MIN(start) FROM genes;
SELECT MAX(start) FROM genes;
SELECT SUM(start) FROM genes;
SELECT AVG(start) FROM genes;
SELECT ROUND(AVG(start)) FROM genes;
SELECT UPPER(entity) FROM genes;
SELECT LOWER(entity) FROM genes;
SELECT LENGTH(entity) FROM genes;
SELECT NOW();
SELECT MAX(start) FROM genes;
SELECT entity, MAX(start) FROM genes GROUP BY entity;

Output:

example=# SELECT * from genes;
 pk | gene_id |   entity   | source | start | stop | sequencing_pk | ec_pk
----+---------+------------+--------+-------+------+---------------+-------
  1 | Gene1   | Chromosome | ena    |   190 |  255 |             1 |     1
  2 | Gene2   | Chromosome | ena    |   375 |  566 |             1 |     2
  3 | Gene3   | Chromosome | ena    |   780 |  980 |             1 |     1
  4 | Gene4   | Plasmid    | ena    |  1001 | 1111 |             1 |     2
  5 | Gene5   | Plasmid    | ena    |   786 |  888 |             1 |     2
(5 rows)


example=# SELECT COUNT(*) FROM genes;
 count
-------
     5
(1 row)


example=# SELECT MIN(start) FROM genes;
 min
-----
 190
(1 row)


example=# SELECT MAX(start) FROM genes;
 max
------
 1001
(1 row)


example=# SELECT SUM(start) FROM genes;
 sum
------
 3132
(1 row)


example=# SELECT AVG(start) FROM genes;
         avg
----------------------
 626.4000000000000000
(1 row)


example=# SELECT ROUND(AVG(start)) FROM genes;
 round
-------
   626
(1 row)


example=# SELECT UPPER(entity) FROM genes;
   upper
------------
 CHROMOSOME
 CHROMOSOME
 CHROMOSOME
 PLASMID
 PLASMID
(5 rows)


example=# SELECT LOWER(entity) FROM genes;
   lower
------------
 chromosome
 chromosome
 chromosome
 plasmid
 plasmid
(5 rows)


example=# SELECT LENGTH(entity) FROM genes;
 length
--------
     10
     10
     10
      7
      7
(5 rows)


example=# SELECT NOW();
              now
-------------------------------
 2021-10-27 12:06:02.175913+01
(1 row)


example=# SELECT MAX(start) FROM genes;
 max
------
 1001
(1 row)


example=# SELECT entity, MAX(start) FROM genes GROUP BY entity;
   entity   | max
------------+------
 Plasmid    | 1001
 Chromosome |  780
(2 rows)

Wednesday 27 October 2021, 465 views