Enough SQL to ask useful questions.

1. SELECT: choosing data

When you open a file, OliMat creates the data table.

-- First 100 rows
SELECT * FROM data LIMIT 100;

-- Only some columns
SELECT date, species, catch_kg
FROM data;

2. Convert text to date and filter a range

TRY_CAST returns null when a value cannot be converted, so one badly written date does not stop the whole query.

SELECT *, TRY_CAST(date_text AS DATE) AS date_ok
FROM data
WHERE TRY_CAST(date_text AS DATE)
  BETWEEN DATE '2025-01-01' AND DATE '2025-12-31';

To compare with a date from another table:

SELECT a.*, b.closing_date
FROM operations a
JOIN calendar b
  ON TRY_CAST(a.date_text AS DATE) = b.closing_date;

3. Summarize with GROUP BY

SELECT species,
       COUNT(*) AS operations,
       SUM(catch_kg) AS total_catch,
       AVG(sea_temp_c) AS avg_temperature
FROM data
GROUP BY species
ORDER BY total_catch DESC;

4. Join files with JOIN

Open the first file and use Add file as table for the second one.

SELECT c.date, c.species_id, s.species_name, c.catch_kg
FROM catches c
LEFT JOIN species s
  ON c.species_id = s.species_id;

5. Attach the closest measurement in time

ASOF JOIN is handy for catches and oceanographic observations.

SELECT c.*, o.temperature, o.chlorophyll
FROM catches c
ASOF LEFT JOIN oceanography o
  ON c.date >= o.date
 AND c.station_id = o.station_id;

6. Functions that pay off

7. If the query fails

The application should not close: it shows the DuckDB error and keeps the editor open so you can fix the query. You can cancel heavy operations and use Plan to inspect the execution.

Official DuckDB documentation ↗