How do I get the value of a column to have a decimal length of 3 in sqlite3?

Question

here

I am trying to divide the value of one column by another column and have the results in a new column, rounded to the the thousands place. TIA!

Here is the code I tried:

%%sql
--#WHICH 5 CASHIERS RANG UP THE MOST CUSTOMERS ON AVERAGE PER SHIFT?
WITH customers_seen AS (SELECT E.NAME,
                               E.EMPLOYEE_ID,
                               E.POSITION,
                               COUNT(DISTINCT T.TRANSACTION_ID) AS CUSTOMERS_SERVICED,
                               S.STORE_BRANCH
                        FROM TRANSACTIONS T
                        JOIN EMPLOYEES E
                        ON E.EMPLOYEE_ID = T.CASHIER_ID
                        JOIN STORES S
                        ON S.STORE_ID = T.STORE_ID
                        GROUP BY E.NAME
                        ORDER BY CUSTOMERS_SERVICED DESC),

number_of_shifts AS (SELECT E.NAME,
                            E.EMPLOYEE_ID,
                            COUNT(DISTINCT T.DATE) AS NUMBER_OF_SHIFTS,
                            E.SHIFT,
                            E.POSITION
                     FROM TRANSACTIONS T
                     JOIN EMPLOYEES E
                     ON E.EMPLOYEE_ID = T.CASHIER_ID
                     GROUP BY E.NAME
                     HAVING E.POSITION = 'cashier'
                     ORDER BY NUMBER_OF_SHIFTS DESC)

SELECT n.NAME,
       c.CUSTOMERS_SERVICED,
       n.NUMBER_OF_SHIFTS,
       ROUND(printf("%.1f",c.CUSTOMERS_SERVICED/n.NUMBER_OF_SHIFTS),4) AS CUSTOMERS_SERVICED_PER_SHIFT
FROM number_of_shifts n
JOIN customers_seen c
ON n.EMPLOYEE_ID = c.EMPLOYEE_ID
ORDER BY CUSTOMERS_SERVICED_PER_SHIFT DESC
LIMIT 5;

and I get this as a result where the value was rounded: enter image description here

Answer 1

I believe your issue is that you are rounding the formatted (printf'ed) output and thus losing the trailing 0's.

Try just printf('%.3f',c.CUSTOMERS_SERVICED/n.NUMBER_OF_SHIFTS))

• note the above may need amending; it has not itself been tested but is based upon the following demonstration code.

Perhaps consider the following example:-

DROP TABLE IF EXISTS ex;
CREATE TABLE IF NOT EXISTS ex (col1 REAL);
INSERT INTO ex VALUES (1234567.1234567),(7654321.7654321),(654321.654321),(54321.54321),(366 * 500);
SELECT *,
    /* Premature rounding*/
    ROUND(printf('%.3f',col1 / 366),3) AS ex01,
    ROUND(printf('%.3f',col1),4) AS ex02,
    /* pre formatting explicit rounding */
    printf('%.3f',round(col1),3) AS ex03,
    printf('%.3f',round(col1/366),3) AS ex04,
    /* pre formatting implicit rounding by printf */
    printf('%.3f',col1/366,3) AS ex05,
    printf('%.3f',col1/366.1234) AS ex06,
    printf('%.9f',col1/366.1234) AS ex07
FROM ex;
DROP TABLE IF EXISTS ex;

This results in:-

and:-

1. ex01 and ex02 are not what is required as rounded but trailing 0's are dropped (see 4th row for value of 54321.54321)
2. ex03 and ex04 are rounded to integers BUT formatted to 3 dp
3. ex05 and ex06 are rounded to 3dp
   1. ex06 is a number close to the original mainly to show values that result in trailing 0's as then not having trailing 0's
4. ex07 shows the value behind ex06 to 9dp (to confirm rounding)