Grouping by Total

1

Hi!

I have this SQL that Groups orders by EAN. But is it possible in the output file to show the total price for each EAN? So I would like num,ber of orders for each EAN and the total sum of Price?

SELECT top 100000
o.ID,
o.Identifier as 'Order Identifier',
o.CreatedAt,
c.FirstName,
c.LastName,
o.CountryIsoCode,
o.IsReturnOrder,
o.ParentID,
o.ExternalReferenceNumber,
o.StoreNo,
o.RoundedPriceSum,
o.ReceiptNo,
p.Identifier as 'ITEM',
p.Family as 'ITEM_PARENT',
p.Name,
p.NameShort,
p.EAN,
p.RemovalCode,
p.SpecEvent,
p.InStock,
m.Name as 'BRAND',
p.VoVgr as 'SubClass',
oi.Coupon,
oi.CouponTotalSum,
oi.Price
FROM StarOrder o
INNER JOIN StarOrderItem oi on oi.OrderID = o.ID
INNER JOIN StarCustomer c on c.ID = o.CustomerID
INNER JOIN StarProduct p on p.ID = oi.ItemID
INNER JOIN StarManufacturer m on m.ID = p.ManufacturerID
where m.Name='Bobbi Brown'
and o.CreatedAt between '2015/11/27' and '2015/11/29'
ORDER BY P.EAN;

2 
oi.Price,
COUNT(o.[ID]) OVER(PARTITION BY p.[EAN]) CountEAN,
SUM(oi.[Price]) OVER(PARTITION BY p.[EAN]) SumEAN
FROM StarOrder
3

Hi

So it should resemble this?

SELECT top 100000
o.ID,
o.Identifier as 'Order Identifier',
o.CreatedAt,
c.FirstName,
c.LastName,
o.CountryIsoCode,
o.IsReturnOrder,
o.ParentID,
o.ExternalReferenceNumber,
o.StoreNo,
o.RoundedPriceSum,
o.ReceiptNo,
p.Identifier as 'ITEM',
p.Family as 'ITEM_PARENT',
p.Name,
p.NameShort,
p.EAN,
p.RemovalCode,
p.SpecEvent,
p.InStock,
m.Name as 'BRAND',
p.VoVgr as 'SubClass',
oi.Coupon,
oi.CouponTotalSum,
oi.Price,
COUNT(o.[ID]) OVER(PARTITION BY p.[EAN]) CountEAN,
SUM(oi.[Price]) OVER(PARTITION BY p.[EAN]) SumEAN
FROM StarOrder
INNER JOIN StarOrderItem oi on oi.OrderID = o.ID
INNER JOIN StarCustomer c on c.ID = o.CustomerID
INNER JOIN StarProduct p on p.ID = oi.ItemID
INNER JOIN StarManufacturer m on m.ID = p.ManufacturerID
where m.Name='Bobbi Brown'
and o.CreatedAt between '2015/11/27' and '2015/11/29'
ORDER BY P.EAN;

this did not work

4

Can you post some sample data.