Types, CAST and CASE

Every expression in SQL has a type, decided before a single row is read, and the type decides the arithmetic. Most "the report is slightly wrong" bugs are a type doing exactly what it was told.

Integers divide like integers

7 / 2 is two integers, so the answer is an integer: 3, with the remainder thrown away. Make one side exact-decimal and you get 3.5. The standard way to change a type is CAST(expr AS type); PostgreSQL also accepts the shorthand expr::type, which MySQL and SQL Server reject.

1SELECT2  7 / 2                     AS int_division,3  CAST(7 AS numeric) / 2    AS numeric_division,4  7 / 2.0                   AS decimal_literal,5  CAST('42' AS integer) + 1 AS text_to_int,6  CAST('2024-02-29' AS date) AS leap_day,7  CAST(3.7 AS integer)      AS to_integer;

The last column is a portability trap: casting 3.7 to an integer gives 4 in PostgreSQL and MySQL (where the target type is spelt SIGNED), which round, while SQL Server truncates to 3. If you mean a particular rounding, say it with ROUND, FLOOR or CEILING before the cast.

A cast can also fail. CAST('12abc' AS integer) is an error in PostgreSQL ("invalid input syntax for type integer"), not a 12 and not a NULL — one bad row stops the whole query, which is why data from a text column gets validated before it gets cast.

Exact against approximate

numeric (also spelt decimal) stores decimal digits exactly, with the precision and scale you declare. real and double precision are binary floating point: fast, wide-ranging, and unable to hold 0.1 exactly.

1SELECT2  CAST(0.1 AS double precision) + CAST(0.2 AS double precision)3    = CAST(0.3 AS double precision)                       AS float_equal,4  CAST(0.1 AS numeric) + CAST(0.2 AS numeric)5    = CAST(0.3 AS numeric)                                AS numeric_equal,6  CAST(0.1 AS double precision) + CAST(0.2 AS double precision) AS float_sum,7  CAST(12.345 AS numeric(5,2))                            AS scale_two,8  CAST('abc' AS varchar(2))                               AS varchar_two;

Money, quantities and anything a person will reconcile by hand belong in numeric(p, s). Note also what a declared scale does: storing 12.345 in numeric(5,2) rounds it to 12.35, and a varchar(2) cast keeps two characters. A type is a rule about values, and the database applies it.

CASE

CASE is SQL's if-expression. The searched form tests conditions in order and returns the first branch whose condition is true; if none is and there is no ELSE, the result is NULL.

CASE
  WHEN total >= 5000 THEN 'large'
  WHEN total >= 1000 THEN 'medium'
  ELSE 'small'
END

All branches must resolve to one type. CASE WHEN ok THEN 1 ELSE 'none' END fails in PostgreSQL, which tries to read 'none' as an integer.

Conditional aggregation

An aggregate over a CASE counts or sums only the rows you pick, so one GROUP BY can produce several filtered totals side by side:

1CREATE TABLE orders (2  id          int PRIMARY KEY,3  customer_id int  NOT NULL,4  status      text NOT NULL5);6INSERT INTO orders VALUES7  (1, 1, 'paid'), (2, 1, 'cancelled'), (3, 1, 'paid'),8  (4, 2, 'refunded'), (5, 2, 'paid'),9  (6, 3, 'cancelled');

1SELECT2  customer_id,3  COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)  AS count_else_zero,4  COUNT(CASE WHEN status = 'paid' THEN 1 END)         AS count_no_else,5  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)    AS sum_ones,6  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*) AS int_share,7  ROUND(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) * 1.08        / COUNT(*), 2)                                AS paid_share9FROM orders10GROUP BY customer_id11ORDER BY customer_id;

The first column is the classic slip. COUNT counts non-null values, and ELSE 0 is not null, so it counts every row. Either SUM the ones and zeros, or COUNT a CASE with no ELSE. The fourth column divides two integers and gets 0 for everyone; multiplying by 1.0 first makes the division exact.

Common mistakes

• Dividing two counts. paid / total with integer counts truncates to 0 or 1. Cast, or multiply by 1.0, before dividing.
• COUNT(CASE ... ELSE 0 END). Counts every row, as the conditional example shows.
• Storing money in double precision. 0.1 + 0.2 is not 0.3 there, and sums drift.
• Relying on how a cast rounds. Engines differ; round explicitly.

What interviewers ask

• What does SELECT 5 / 2 return, and how do you get 2.5? 2, because both operands are integers; cast one side to , or write .