Integer Division Drops the Fraction on SQLite and PostgreSQL and Keeps It on MySQL

You write an average as SUM(stock) / COUNT(*), try it on SQLite and get 3. MySQL gives 3.3333 for the same rows. Both are doing what they were built to do, and they disagree about what dividing two integers means.

SQLite and PostgreSQL give an integer result when both sides of / are integers, so 7 / 2 is 3. MySQL and MariaDB always divide as decimals and answer 3.5000. Averages written as SUM(x) / COUNT(*) are where this usually bites: the sum of an integer column is an integer on SQLite and PostgreSQL, so the average loses its fraction there.

What Each Engine Answered

The example

SELECT 7 / 2;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT ROUND(7 * 1.0 / 2, 2);

Multiply one side by 1.0 for the fraction and round it to the places you need (see Dividing decimals): ROUND(7 * 1.0 / 2, 2) is 3.5 on every engine. For a whole number on every engine, take the remainder off first: (7 - 7 % 2) / 2.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: Dividing an integer by an integer drops the fraction on SQLite and PostgreSQL; MySQL and MariaDB keep it. [integer-division]
    7 / 2
    Multiply one side by 1.0 for the fraction and round it to the places you need (see decimal-division): ROUND(7 * 1.0 / 2, 2) is 3.5 on every engine. For a whole number on every engine, take the remainder off first: (7 - 7 % 2) / 2.

1 problem: 1 warning.

baresql rules integer-division prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.