[RESOLVED] SQL Ignoring Distinct Data

Rampage-Obidos

I am struggling to work out how I can get this query to work correctly. It returns multiple PLU's from the PLUHistory table, despite having the distinct clause.

I have read somewhere about a limit to the tables you can select with distinct, but I'm not 100% sure on what that limit is, if it applies here, or how to get around it.

I did think that I might be able to do a nested query, however I have never attempted it or even understand them fully.

SELECT DISTINCT PLUHistory.[PLU], PLU.[PLU], Sum(Sales) AS [PooleSales],[StoreNumber],[HistoryDate],[SupplierID],[SupplierCode],(Sum(Sales) * [SalePrice1]) AS SalesValue,[ProductDescription],[TotalStock],[CaseQty],[InnerQty],[FlagForOrder],[PLUState],[PLUStateDescription],[ProductOutline] 
FROM PLUHistory
LEFT JOIN PLU ON PLU.PLU = PLUHistory.PLU 
LEFT JOIN Stock ON PLU.PLU = Stock.StockID 
LEFT JOIN PLUState ON PLU.PLUState = PLUState.PLUStateID  

WHERE CONVERT(CHAR(10),HistoryDate,120)>='$SoldIn' AND [StoreNumber]='$Store' AND [Sales]>'0' AND [SupplierID]='$Supplier' AND [StoreID]='$Store' 
GROUP BY PLUHistory.PLU,StoreNumber,HistoryDate,PLU.PLU,SupplierID,Sales,SalePrice1,SupplierCode,ProductDescription,TotalStock,CaseQty,InnerQty,FlagForOrder,PLUState,PLUStateDescription,ProductOutline 
ORDER BY $Sort $ASC

If anyone has any suggestions on how I can get it to work I would be most appreciative.

Thanks

Rampage-Obidos

Well now I'm red faced, after a little bit of re-jigging, I noticed I had the 'HistoryDate' in the select table section. Removing it stopped the repeat of PLU's and allowed the 'Sum(Sales) AS [PooleSales]' to work as it should.

Hope this helps some one in future.

Thanks