Excel to SQL

Filter#

WHERE keeps the rows that match and returns only those. The condition is written into the query, so anybody who runs it again gets the same rows.

SELECT "OrderID", "OrderDate", "Freight"
FROM orders
WHERE "ShipCountry" = 'Germany'

See: §7. WHERE

Sort#

Each sort level in Excel's Sort dialog becomes one column in ORDER BY, in the same order. DESC sorts that column largest first.

SELECT "ShipCountry", "OrderID", "Freight"
FROM orders
ORDER BY "ShipCountry", "Freight" DESC

See: §8. ORDER BY and LIMIT

Remove Duplicates#

SELECT DISTINCT returns each combination of the listed columns once and leaves the table untouched. It also keeps one row for a missing value. Customers come from 21 named countries, and the query below returns 22 rows, because two customers have no country.

SELECT DISTINCT "Country"
FROM customers

See: §10. DISTINCT, and what it hides

COUNTIFS#

Conditions that apply to every count go in WHERE. A condition that applies to one count goes in FILTER, which lets several counts sit side by side. Of 2,193 orders shipped to Germany, 1,720 carried more than 100 in freight.

SELECT
    count(*)                                AS german_orders,
    count(*) FILTER (WHERE "Freight" > 100) AS over_100_freight
FROM orders
WHERE "ShipCountry" = 'Germany'

In T-SQL: SQL Server rejects FILTER. Write SUM(CASE WHEN "Freight" > 100 THEN 1 ELSE 0 END), which also runs in DuckDB.

See: §13. Aggregate functions

SUMIFS#

Each criteria range and criterion becomes one condition in WHERE, joined with AND. Freight on orders shipped to Germany in 2016 comes to 50,361.50.

SELECT round(sum("Freight"), 2) AS freight_2016
FROM orders
WHERE "ShipCountry" = 'Germany'
  AND year("OrderDate") = 2016

See: §13. Aggregate functions

AVERAGEIFS#

Put the conditions in WHERE and average with avg. The query below averages the quantity on discounted order lines.

SELECT round(avg("Quantity"), 2) AS avg_quantity
FROM "Order Details"
WHERE "Discount" > 0

In T-SQL: SQL Server averages a column of integers as integers and drops the fraction. Write AVG("Quantity" * 1.0).

See: §13. Aggregate functions

PivotTable#

The fields in the Rows area go in GROUP BY, and each field in the Values area becomes an aggregate. A PivotTable picks Sum or Count from the column type, and SQL makes you name the aggregate.

SELECT "ShipCountry", count(*) AS orders, round(sum("Freight"), 2) AS freight
FROM orders
GROUP BY "ShipCountry"
ORDER BY freight DESC

See: §14. GROUP BY

PivotTable Columns area#

Each value you would drag into the Columns area becomes its own aggregate with its own condition.

SELECT
    "ShipCountry",
    count(*) FILTER (WHERE year("OrderDate") = 2022) AS orders_2022,
    count(*) FILTER (WHERE year("OrderDate") = 2023) AS orders_2023
FROM orders
GROUP BY "ShipCountry"
ORDER BY "ShipCountry"

In T-SQL: write each column as SUM(CASE WHEN YEAR("OrderDate") = 2022 THEN 1 ELSE 0 END).

See: §14. GROUP BY

VLOOKUP and XLOOKUP#

A lookup is a join. The query below brings each product's supplier name across from suppliers.

SELECT p."ProductName", s."CompanyName" AS supplier
FROM products AS p
LEFT JOIN suppliers AS s ON s."SupplierID" = p."SupplierID"

A join differs from a lookup formula in two cases. When nothing matches, VLOOKUP shows #N/A and LEFT JOIN returns NULL, while INNER JOIN drops the row. And VLOOKUP stops at the first match, while a join returns every match. Look up in the other direction, from suppliers to products, and one supplier comes back as five rows.

See: §18. LEFT JOIN, and what it preserves · §19. When a JOIN duplicates rows

IF#

CASE WHEN tests conditions in order and returns the first one that is true. List the missing case first. A NULL in a comparison is neither true nor false, so without that line the 21 orders that never shipped would fall through to ELSE and be labeled on time.

SELECT
    "OrderID",
    CASE
        WHEN "ShippedDate" IS NULL THEN 'not shipped'
        WHEN "ShippedDate" > "RequiredDate" THEN 'late'
        ELSE 'on time'
    END AS delivery
FROM orders

In T-SQL: CASE WHEN is the same. SQL Server also has IIF(condition, if_true, if_false), which DuckDB rejects.

See: §11. CASE WHEN

IFERROR#

SQL has no general catch for an error. The error you would usually wrap is a division by zero, and NULLIF(denominator, 0) turns a zero denominator into NULL, so the division returns NULL instead of failing.

SELECT "ProductName", "UnitsInStock" / NULLIF("UnitsOnOrder", 0) AS stock_to_orders
FROM products

See: §9. NULL, and why it is not zero

ISBLANK#

IS NULL tests for a missing value, and coalesce replaces it with a value you choose. Writing = NULL returns no rows at all.

SELECT "CompanyName", coalesce("Country", 'unknown') AS country
FROM customers
WHERE "Country" IS NULL

See: §9. NULL, and why it is not zero

CONCAT#

concat_ws joins values with a separator and skips a missing one, which is what & with a ", " between cells does until a cell is blank.

SELECT "CompanyName", concat_ws(', ', "City", "Country") AS location
FROM customers

In T-SQL: CONCAT_WS is the same.

TEXTJOIN#

string_agg joins the values from many rows into one, which is TEXTJOIN over a range.

SELECT "Country", string_agg("CompanyName", ', ' ORDER BY "CompanyName") AS customers
FROM customers
GROUP BY "Country"

In T-SQL: the order goes after the call, as STRING_AGG("CompanyName", ', ') WITHIN GROUP (ORDER BY "CompanyName").

LEFT, MID and LEN#

left and right work as they do in Excel. substring(text, start, length) is MID, and counting starts at 1 in both. length is LEN.

SELECT "ProductName", left("ProductName", 3) AS first_three, length("ProductName") AS name_length
FROM products

In T-SQL: LEFT and SUBSTRING are the same, and the length function is LEN. LEN ignores spaces at the end of the text, so LEN('Chai ') is 4 where DuckDB's length('Chai ') is 5.

TRIM and UPPER#

upper and lower work as they do in Excel, and T-SQL spells them the same way. trim removes the spaces at both ends, and unlike Excel's TRIM it leaves a run of spaces between two words alone, so trim(' A B ') is 'A B'. To squeeze those to one space as well, use regexp_replace.

SELECT upper(regexp_replace(trim("CompanyName"), ' +', ' ', 'g')) AS company
FROM customers

In T-SQL: TRIM also removes the ends only. REGEXP_REPLACE takes no 'g', because it replaces every match by default and its fourth argument is a start position, so the line is UPPER(REGEXP_REPLACE(TRIM("CompanyName"), ' +', ' ')).

TODAY#

current_date returns today's date.

SELECT current_date AS today

In T-SQL: CAST(GETDATE() AS date). GETDATE() alone returns a date and a time.

YEAR and MONTH#

year and month take a date and return a number, in DuckDB and T-SQL alike. The query below counts orders by year, starting from the earliest.

SELECT year("OrderDate") AS order_year, count(*) AS orders
FROM orders
GROUP BY year("OrderDate")
ORDER BY order_year

See: §12. Dates: truncating, differencing, grouping by month

DATE#

make_date(year, month, day) builds a date from three numbers.

SELECT count(*) AS orders_since_2020
FROM orders
WHERE "OrderDate" >= make_date(2020, 1, 1)

In T-SQL: DATEFROMPARTS(2020, 1, 1).

EOMONTH#

last_day returns the last day of a date's month.

SELECT "OrderID", "OrderDate", last_day("OrderDate") AS month_end
FROM orders

In T-SQL: EOMONTH, which DuckDB rejects.

See: §12. Dates: truncating, differencing, grouping by month

DATEDIF#

date_diff('day', start, end) counts the days from start to end, with the unit first where DATEDIF puts it last.

SELECT "OrderID", date_diff('day', "OrderDate", "ShippedDate") AS days_to_ship
FROM orders

In T-SQL: DATEDIFF(DAY, start, end).

See: §12. Dates: truncating, differencing, grouping by month

ROUND#

round(value, digits) rounds as Excel's ROUND does, and T-SQL spells it the same way.

SELECT "OrderID", round("Freight" * 1.08, 2) AS freight_with_tax
FROM orders

Running total#

A formula such as =SUM($B$2:B2) copied down a column becomes a window function: sum(...) OVER (ORDER BY ...) adds up every row up to the current one.

SELECT
    order_month,
    orders,
    sum(orders) OVER (ORDER BY order_month) AS orders_to_date
FROM (
    SELECT date_trunc('month', "OrderDate") AS order_month, count(*) AS orders
    FROM orders
    GROUP BY date_trunc('month', "OrderDate")
)
ORDER BY order_month

See: §23. Window functions

Percent of total#

sum(...) OVER () with nothing in the parentheses is the total of the whole column, repeated on every row, so dividing by it gives each row's share. That is =B2/SUM($B$2:$B$100) without the fixed range.

SELECT
    "ShipCountry",
    count(*) AS orders,
    round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS percent_of_orders
FROM orders
GROUP BY "ShipCountry"
ORDER BY orders DESC

See: §23. Window functions