Skip to content

Replicating common spreadsheet tasks in SQL

Remove duplicate rows - UNION


-- Combines (appends) columns a, b and c from table 1 and 2
-- and removes any duplicate rows that have equivalent values
-- across all 3 columns.

SELECT
    column_a,
    column_b,
    column_c,
FROM table_1

UNION

SELECT
    column_a,
    column_b,
    column_c,
FROM table_2;

Appending one table to the bottom of another - UNION ALL


-- Combines (appends) columns a, b and c from table 1 and 2
-- Does not remove any rows.
-- The total number of rows in the output below is the total rows in table_1 plus total rows in table_2

SELECT
    column_a,
    column_b,
    column_c,
FROM table_1

UNION ALL

SELECT
    column_a,
    column_b,
    column_c,
FROM table_2;

Filtering rows of data- WHERE


-- Filtering rows of data can be achieved by using the WHERE cluase
-- only keep where the value in column_a is 1
-- and only keep rows where column_b is not 'this'

SELECT
    column_a,
    column_b,
    column_c,
FROM table_1
WHERE column_a = 1
    AND column_b != 'this';

vlookups - LEFT JOIN

IF function - CASE WHEN

SUM & SUMIF functions

COUNT & COUNTIF function

TODAY function

Creating a date series

Creating a numeric series