ship ratio равен 0, а pct для reno неверный, SQLite

Обновление: кажется, дело в join. Каждый заказ повторяется по разу на каждую строку, поэтому COUNT(*) считает строки, а не заказы. Может, COUNT(DISTINCT o.id) это исправит? И 1.0 * для ratio...

Может, COUNT(DISTINCT o.id) это исправит? И 1.0 * для ratio...

Да, оба варианта верны, но числитель тоже нужно исправить. Ваши 66.7 для reno — это 4/6. Join даёт 6 строк, потому что у некоторых заказов по две строки, и COUNT(o.shipped_at) считает такие заказы дважды. Если сделать distinct только знаменатель, получится 4 отгружено / 4 заказа = неверно, только в другую сторону. Обе стороны должны считаться по уникальным заказам.

Ratio равен 0, потому что оба операнда целые, и SQLite делает целочисленное деление. 3/4 даёт 0. Если сначала умножить на 1.0, результат станет вещественным. Смотрите раздел "Operators" в https://www.sqlite.org/lang_expr.html

SELECT o.warehouse,
       COUNT(DISTINCT o.id) AS orders,
       COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END) AS shipped,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END)
             / COUNT(DISTINCT o.id), 1) AS ship_pct,
       SUM(l.qty) AS units
FROM orders o
JOIN order_lines l ON l.order_id = o.id
GROUP BY o.warehouse;

SUM(l.qty) можно оставить как есть, потому что он суммирует по строкам. Кроме того, inner JOIN отбрасывает заказы без единой строки, так что ваши 4 для reno всё равно могут отличаться от простого подсчёта. Если так, переключитесь на LEFT JOIN. Запустите и сравните с ручным подсчётом.

Обе стороны должны считаться по уникальным заказам.

Только если сначала делать join. Я бы суммировал qty по заказам в подзапросе, а потом делал join. Тогда обычный COUNT(*) работает, и DISTINCT не нужен.

Это работает, но "DISTINCT не нужен" верно только если подзапрос даёт ровно одну строку на заказ и join сохраняет каждый заказ. При inner join заказ без строк пропадает со стороны подзапроса, и COUNT(*) молча занижает результат. Это был вопрос про 4 у reno из #3, и вы на него пока не ответили.

SELECT o.warehouse,
       COUNT(*) AS orders,
       COUNT(o.shipped_at) AS shipped,
       ROUND(100.0 * COUNT(o.shipped_at) / COUNT(*), 1) AS ship_pct,
       COALESCE(SUM(t.units), 0) AS units
FROM orders o
LEFT JOIN (SELECT order_id, SUM(qty) AS units
           FROM order_lines
           GROUP BY order_id) t ON t.order_id = o.id
GROUP BY o.warehouse;

Две детали. Это обязательно должен быть LEFT JOIN, а units равен NULL для заказов без строк (отсюда COALESCE). 100.0 * по-прежнему нужен, потому что целочисленное деление из исходного запроса никуда не делось.

(Честно говоря, я бы разбил это на два запроса и сравнил количество заказов. Это дешёвая проверка.)