SQLTeam.com | Weblogs | Forums

Groupyng By and getting price average


#1

Hi, I have a sql 2008 view that shows me sales records, I need to group and calculate the average sale price but considering that we had items sold to zero price, thats records must be on other row, example

I HAVE THIS
YearNr|MonthNr|DebtorNr| Debtor |ItemCode| SalePrice|CostPrice|Currency|QTY|Subtotal|Tax|Discount|Total|
2015 | 02 | 10001 | Debtor1 | ITM-01 | 10.00 | 8.00 | USD | 2 | 20.00 | 0.0| 0.00 |20.00
2015 | 02 | 10001 | Debtor1 | ITM-01 | 8.00 | 6.00 | USD | 3 | 24.00 | 0.0 | 0.00 |24.00
2015 | 02 | 10001 | Debtor1 | ITM-01 | 0.00 | 6.00 | USD | 1 | 0.00 | 0.0 | 100.00 | 0.00

I NEED THIS

YearNr|MonthNr|DebtorNr| Debtor |ItemCode| SalePAv|CostPAv|Currency|QTY|Subtotal|Tax|Discount|Total|Recs
2015 | 02 | 10001 | Debtor1 | ITM-01 | 9.00 | 14.00 | USD | 5 | 44.00 | 0.0| 0.00 |44.00| 2
2015 | 02 | 10001 | Debtor1 | ITM-01 | 0.00 | 6.00 | USD | 1 | 0.00 | 0.0 | 100.00 | 0.00| 1

The salePAv (Average) Formule is sum (SalePrice) / Recs
The Recs are the records counted, the records with SalePrice 0 are counted apart.

THIS IS MY CODE

SELECT COUNT(ItemCode) AS NRecords, YearNr, MonthNr, InvoiceType, DebtorNr, Debtor, SubTotal, TaxAmount, DiscountPerc, DiscountAmount, Total, Cur, ItemCode, Item, Unit, SalePrice, CostPrice, Qty,
Rep
FROM dbo.ViewVK_Facturas
GROUP BY YearNr, MonthNr, InvoiceType, DebtorNr, Debtor, SubTotal, TaxAmount, Total, Cur, ItemCode, Item, Unit, SalePrice, CostPrice, Qty, DiscountPerc, DiscountAmount, Rep

Thanks for your help


#2

Add another condition to your group by clause to separate out rows that have SalePrice = 0 like this:

GROUP BY
    CASE WHEN SalePrice = 0 THEN 0 ELSE 1 END,
    -- your other group by conditions
   YearNr, 
   MonthNr,
.....

#3

Thank You so much JamesK, it works.