need help to figure out average price markup on products, I have cost and selling price

Question

I have a database that has products in it, with both the cost and selling price.

I want to create a simple report that will group similar costing, and figure out how much markup there is.

I will then use this to apply the markup on new products.

So my database looks like:

productid, costPrice, sellingPrice
123, 9.99, 14.99

I have new products that only have the cost price, so I want to figure out what the average selling price is for products with the same cost, and apply it on new products.

Answer 1

Well firstly, your markup is calculated as (Selling/Cost).

In your case 14.99/9.99=1.5005005005....

If you wish to check the avg, it should be something like

SELECT CostPrice, AVG(SellingPrice/CostPrice)
FROM Table
GROUP BY CostPrice

This will not make a lot on sense, unless you use a price range, of product categories.

Certain products might have greater markup than others, but still come to the same selling price. You might want to approach this slightly differently.

To work with your ranges, I might go wiht something like

DECLARE @Range INT
SET @Range = 5

SELECT  CEILING(CostPrice / @Range) * @Range,
        AVG(SellingPrice / CostPrice)
FROM    @Table
GROUP BY CEILING(CostPrice / @Range)

Or create a @table that contains your price ranges, and group by these ranges.

Something like

DECLARE @Table TABLE(
        CostPrice FLOAT,
        SellingPRice FLOAT
)

INSERT INTO @Table SELECT 0.99, 14.99
INSERT INTO @Table SELECT 4.99, 9.99
INSERT INTO @Table SELECT 9.99, 14.99
INSERT INTO @Table SELECT 19.99, 9.99

DECLARE @Ranges TABLE(
        StartVal FLOAT,
        EndVal FLOAT
)

INSERT INTO @Ranges SELECT 0, 5
INSERT INTO @Ranges SELECT 5, 10
INSERT INTO @Ranges SELECT 10, 15
INSERT INTO @Ranges SELECT 15, NULL

SELECT  ISNULL(r.EndVal, r.StartVal),
        AVG(t.SellingPRice / t.CostPrice)
FROM    @Ranges r LEFT JOIN
        @Table t    ON  t.CostPrice > r.StartVal AND (t.CostPrice <= r.EndVal OR r.EndVal IS NULL)
GROUP BY ISNULL(r.EndVal, r.StartVal)