Given sale price and qty sold and invoice id, how would I get the average per unit price…
- 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?
- 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_soldgives 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.
NULLIFprevents division-by-zero when no units were sold.
- User
Is there an equivalent to TRUNC in SQL that I could use to cap the number of decimals returned
- Assistant
In SQL Server you have two common ways to “cap” (truncate, not round) the number of decimal places:
-
TRUNCATE the value – SQL Server has no
TRUNCscalar function, but you can mimic it withROUNDand a little math:-- Truncate @val to 2 decimal places DECLARE @val DECIMAL(18,6) = 123.456789; SELECT TRUNCATED = ROUND(@val, 2, 1); -- 123.45The third parameter
1tellsROUNDto truncate instead of round. -
FORMAT the display only – if you only want to control what the client sees (and don’t care about the underlying value), use
FORMATorSTR: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) -
- User
Would N4 mean that I get four decimal places when used in conjunction with format?
- 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
FORMATwith the truncation expression:SELECT FORMAT(FLOOR(value * 10000) / 10000.0, 'N4'); -- 123.4567 - User
What about spark sql/within microsoft fabric? I'm having issues
- Assistant
In Spark SQL (including Microsoft Fabric / Synapse),
FORMATandROUNDbehave differently than in T-SQL, and there is no directTRUNCscalar.
Below are the patterns that work in Spark SQL to cap / truncate a number to exactly 4 decimal places (no rounding).
- 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.00005prevents the unwanted “round-half-up” behavior.
- 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.
- 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_NUMBERreturns 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:
original truncated str_4d 123.456789 123.4567 123.4567 Use whichever pattern matches your need (numeric vs. string output).
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.