# Average Cost By Material Type

**URL:** https://forum.innovativeusers.org/t/average-cost-by-material-type/2658
**Category:** Polaris Repository
**Created:** [October 6, 2025, 2:36pm UTC](https://forum.innovativeusers.org/t/average-cost-by-material-type/2658 "2025-10-06T14:36:28Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![mhammermeister](https://forum.innovativeusers.org/letter_avatar_proxy/v4/letter/m/c57346/32.png) [@mhammermeister](https://forum.innovativeusers.org/u/mhammermeister)
#### Post date: [October 6, 2025, 2:36pm UTC](https://forum.innovativeusers.org/t/average-cost-by-material-type/2658/1 "2025-10-06T14:36:28Z")

</div>

This was a response to a request on Discord, looking to modify the “Expenditures by Material Type and Fund” canned report to include deleted materials. Posting here for posterity.

```sql
SELECT 
    fy.FiscalYearID,
    fy.Name AS FiscalYearName,
    fu.FundID,
    fu.Name + N': ' + fu.AlternativeName AS FundName,
    mt.MaterialTypeID,
    mt.Description AS MaterialType,
    SUM(seg2.quantrec) AS nItems,
    CAST(SUM(ISNULL(lin.DiscPriceUnitBase, 0.00)) AS MONEY) AS Total,
    CAST(SUM(ISNULL(lin.DiscPriceUnitBase, 0.00)) AS MONEY) / SUM(seg2.quantrec) AS AvgCostPerItem
FROM polaris.polaris.FiscalYears fy WITH (NOLOCK)
INNER JOIN polaris.polaris.Funds fu WITH (NOLOCK)
    ON fy.FiscalYearID = fu.FiscalYearID
INNER JOIN polaris.polaris.InvLineItemSegmentAmts seg WITH (NOLOCK)
    ON fy.FiscalYearID = seg.FiscalYearID AND seg.FundID = fu.FundID
INNER JOIN polaris.polaris.InvLineItemSegments seg2
    ON seg.InvLineItemSegmentID = seg2.InvLineItemSegmentID
INNER JOIN polaris.polaris.InvLines lin
    ON seg2.InvLineItemID = lin.InvLineItemID
INNER JOIN polaris.polaris.MaterialTypes mt WITH (NOLOCK)
    ON lin.MaterialTypeID = mt.MaterialTypeID
WHERE seg2.PaymentStatusID IN (7, 9)

GROUP BY 
    fy.FiscalYearID,
    fy.Name,
    fu.FundID,
    fu.Name + N': ' + fu.AlternativeName,
    mt.MaterialTypeID,
    mt.Description
ORDER BY 
    fy.Name,
    fu.Name + N': ' + fu.AlternativeName,
    mt.Description;

```