Search code examples
sqliteanalytics

Replace after decimal SQL

I am learning SQL and trying make an innings pitched field.

SELECT ROUND((outs / 3), 3) AS innings_pitched, player FROM pitching;

Currently on output I get a decimal like: 5.33 (5 and 1/3 IP).

How do I convert the decimal into a standard baseball format:

5 and 1/3 IP should read as 5.1 5 and 2/3 IP should read as 5.2 6 whole IP should read as 6.0

Solution

  • It can be accomplished using the modulo operator (%) to get the remainder and printf to format the output.

    Example:

    SELECT printf("%i.%i",outs/3,outs % 3) as innings_pitched,  player FROM pitching;