Skip to content
The Chat Archive
kimi-k2-0711-preview· Moonshot AI LMArena Human Preference 140K

Given sale price and qty sold and invoice id, how would I get the average per unit price…

  1. User

    Given sale price and qty sold and invoice id, how would I get the average per unit price of a part in a SQL server?

  2. Assistant

    In SQL Server you can compute the average per-unit price for a part with a single, simple aggregation:

    SELECT
        part_id,                       -- or whatever your part identifier column is
        SUM(sale_price * qty_sold) * 1.0 / NULLIF(SUM(qty_sold), 0) AS avg_unit_price
    FROM invoices
    GROUP BY part_id;
    

    Explanation

    • sale_price * qty_sold gives the extended price for each invoice line.
    • SUM(sale_price * qty_sold) is the total revenue for that part across all invoices.
    • SUM(qty_sold) is the total quantity sold for that part.
    • Dividing the two yields the weighted average price per unit.
    • NULLIF prevents division-by-zero when no units were sold.
  3. User

    Is there an equivalent to TRUNC in SQL that I could use to cap the number of decimals returned

  4. Assistant

    In SQL Server you have two common ways to “cap” (truncate, not round) the number of decimal places:

    1. TRUNCATE the value – SQL Server has no TRUNC scalar function, but you can mimic it with ROUND and a little math:

      -- Truncate @val to 2 decimal places
      DECLARE @val DECIMAL(18,6) = 123.456789;
      
      SELECT TRUNCATED = ROUND(@val, 2, 1);   -- 123.45
      

      The third parameter 1 tells ROUND to truncate instead of round.

    2. FORMAT the display only – if you only want to control what the client sees (and don’t care about the underlying value), use FORMAT or STR:

      SELECT FORMAT(@val, 'N2');   -- 123.46  (rounds)
      SELECT FORMAT(FLOOR(@val * 100) / 100.0, 'N2');  -- 123.45  (true truncate)
      

    So the closest built-in equivalent to Oracle’s TRUNC(number, n) in SQL Server is:

    ROUND(your_number, n, 1)
    
  5. User

    Would N4 mean that I get four decimal places when used in conjunction with format?

  6. Assistant

    Yes.
    FORMAT(value, 'N4') tells SQL Server to display the number with exactly four digits after the decimal point (thousand separators included).
    Example:

    SELECT FORMAT(123.456789, 'N4');   -- returns 123.4568  (rounded)
    

    If you want four decimal places without rounding, combine FORMAT with the truncation expression:

    SELECT FORMAT(FLOOR(value * 10000) / 10000.0, 'N4');  -- 123.4567
    
  7. User

    What about spark sql/within microsoft fabric? I'm having issues

  8. Assistant

    In Spark SQL (including Microsoft Fabric / Synapse), FORMAT and ROUND behave differently than in T-SQL, and there is no direct TRUNC scalar.
    Below are the patterns that work in Spark SQL to cap / truncate a number to exactly 4 decimal places (no rounding).


    1. Truncate to 4 decimals (keep the value numeric)
    SELECT
      ROUND(your_col - 0.00005, 4, 'HALF_DOWN')  AS truncated_4d
    FROM your_table;
    
    • ROUND(expr, scale, 'HALF_DOWN') is Spark’s built-in overload that truncates when the third argument is 'HALF_DOWN' (or 'DOWN').
    • your_col - 0.00005 prevents the unwanted “round-half-up” behavior.

    1. Cast to DECIMAL(38,4) (simplest, but still rounds)
    SELECT
      CAST(your_col AS DECIMAL(38,4)) AS rounded_4d
    FROM your_table;
    

    Use this only if rounding is acceptable.


    1. Format to 4 decimals as a string (no rounding)
    SELECT
      FORMAT_NUMBER(FLOOR(your_col * 10000) / 10000, 4) AS str_4d
    FROM your_table;
    
    • FORMAT_NUMBER returns a string with exactly four digits after the decimal.
    • FLOOR(...) guarantees truncation.

    Quick test in Fabric notebook / Spark SQL:

    SELECT
      123.456789 AS original,
      ROUND(123.456789 - 0.00005, 4, 'HALF_DOWN') AS truncated,
      FORMAT_NUMBER(FLOOR(123.456789 * 10000) / 10000, 4) AS str_4d;
    

    Result:

    originaltruncatedstr_4d
    123.456789123.4567123.4567

    Use whichever pattern matches your need (numeric vs. string output).

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.