been messing query volusion store, trying top selling sku's brand.... , have done so, how can show top 10 per brand....
if add top 10 10 rows period.
select products_joined.productmanufacturer brand, sum(orderdetails.productprice * orderdetails.quantity) totalsold, orderdetails.productcode sku orderdetails, orders, products_joined products_joined.productcode = orderdetails.productcode , orders.orderid = orderdetails.orderid , orders.orderdate between getdate() - 90 , getdate() , orders.orderstatus <> 'cancelled' , products_joined.productmanufacturer not null group products_joined.productmanufacturer, orderdetails.productcode order products_joined.productmanufacturer, sum(orderdetails.productprice*orderdetails.quantity) desc
if row_number available, might able use cte's , this.
;with cteproductssold ( select pj.productmanufacturer brand, od.productcode sku, sum(od.productprice * od.quantity) totalsold orders o inner join orderdetails od on od.orderid = o.orderid inner join products_joined pj on pj.productcode = od.productcode o.orderdate between getdate() - 90 , getdate() , o.orderstatus <> 'cancelled' , pj.productmanufacturer not null group pj.productmanufacturer, od.productcode ), cteproductordered ( select *, row_number() on (partition brand order totalsold desc) rn cteproductssold ) select brand, sku, totalsold cteproductordered rn < 11 alternatively, can use derived tables instead of ctes.
select brand, sku, totalsold ( select *, row_number() on (partition brand order totalsold desc) rn ( select pj.productmanufacturer brand, od.productcode sku, sum(od.productprice * od.quantity) totalsold orders o inner join orderdetails od on od.orderid = o.orderid inner join products_joined pj on pj.productcode = od.productcode o.orderdate between getdate() - 90 , getdate() , o.orderstatus <> 'cancelled' , pj.productmanufacturer not null group pj.productmanufacturer, od.productcode ) p ) ps rn < 11 