Thread: Sales report per SKU

Hi

Anybody know of a way to extract a report of the total sales of each SKU for a required date range?

Example - A total sales report for a year of all SKU's that have sold?

Very rough (and you might want to check that I've used the correct product price field) but something like this to get you started...

Code:

select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales 
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
where o.OrderDate between '01-Jan-2011' and '01-Jan-2012'
group by os.OrderedProductSKU 
order by 2 desc

Just needed the

USE [dbname]
GO

before. Seems to work like a charm. Is it also possible to include the Quantity & Ordered ProductName from within the Orders_ShoppingCart table so there is the SKU, Total amount, Product name and Quantity?

Yes, you can add Qty and Product name but you need a slight SQL trick to convert the name field to nvarchar because you can't group by ntext columns:

Code:

select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales,
cast(os.OrderedProductName as nvarchar(255)) Name, SUM(os.Quantity) Qty
 from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
where o.OrderDate between '01-Jan-2011' and '01-Jan-2012'
group by os.OrderedProductSKU, cast(os.OrderedProductName as nvarchar(255))
order by 2 desc

What would be really nice is to have this type of report available in the admin pages.... but I can't do that 'pro bono' I'm afraid. Someone else here might be able to though.

Adam

You can always add it to the custom report area

That's great as it is thanks!

Should be a standard report I think

Great work!