[RESOLVED] Possible Group By Error - Multiple Rows Showing on Distinct

Rampage-Obidos

I am once again in need of someone cleverer than I to help with with an SQL issue.

The below query works, however it brings up multiple lines of the Distinct value. This link will show you what I mean: http://www.gzcf.co.uk/images/Error.jpg

The last time I had a similar situation I managed to solve it by adding/removing bits from the Group By, however I can't see what could be causing it this time.

Any and all help would be greatly appreciated.

Thanks

	SELECT
	DISTINCT HistoryPoole.[PLU],
	HistoryPoole.[HistoryDate],
	Sum(HistorySoton.[Sales]) AS [SotonSales],
	Sum(HistoryPoole.[Sales]) AS [PooleSales],
	(Sum(HistorySoton.[Sales]) + Sum(HistoryPoole.[Sales])) AS [TotalSales],
	StockPoole.[TotalStock] AS [PooleStock],
	StockSoton.[TotalStock] AS [SotonStock],
	(StockPoole.[TotalStock]+StockSoton.[TotalStock]) AS [TotalStock],
	HistoryPoole.[StoreNumber],
	[SupplierID],
	[SupplierCode],
	((Sum(HistoryPoole.[Sales])+Sum(HistorySoton.[Sales])) * [SalePrice1]) AS SalesValue,
	[ProductDescription],
	[CaseQty],
	[InnerQty],
	StockPoole.[FlagForOrder],
	[PLUState],
	[PLUStateDescription],
	[ProductOutline],
	[Season],
	StockPoole.[LastSold] AS [PooleLastSold],
	StockSoton.[LastSold] AS [SotonLastSold]



FROM Eskimo.dbo.PLUHistory AS [HistoryPoole]
LEFT JOIN Eskimo.dbo.PLU AS [PLUPoole] ON PLUPoole.[PLU] = HistoryPoole.[PLU] 
LEFT JOIN Eskimo.dbo.Stock AS [StockPoole] ON PLUPoole.[PLU] = StockPoole.[StockID] 
LEFT JOIN Eskimo.dbo.PLUState AS [StatePoole] ON PLUPoole.[PLUState] = StatePoole.[PLUStateID] 
LEFT JOIN EskimoSoton.dbo.[Stock] AS [StockSoton]  ON PLUPoole.PLU = StockSoton.[StockID] 
LEFT JOIN EskimoSoton.dbo.[PLUHistory] AS [HistorySoton] ON HistoryPoole.[PLU] = HistorySoton.[PLU]  

WHERE
CONVERT(CHAR(10),HistoryPoole.[HistoryDate],120)>='$SoldIn' AND
CONVERT(CHAR(10),HistorySoton.[HistoryDate],120)>='$SoldIn' AND
HistoryPoole.[StoreNumber]='001' AND
HistorySoton.[StoreNumber]='004' AND
(HistoryPoole.[Sales]>'0' OR HistorySoton.[Sales]>'0') AND
[SupplierID]='$Supplier' AND
StockPoole.[StoreID]='001' AND
StockSoton.[StoreID]='004'
$Seasonlist$PLUState 

GROUP BY
HistoryPoole.[PLU],
HistoryPoole.[HistoryDate],
HistorySoton.[HistoryDate],
HistoryPoole.[StoreNumber],
HistorySoton.[StoreNumber],
[SupplierID],
[SalePrice1],
[SupplierCode],
[ProductDescription],
[CaseQty],
[InnerQty],
StockPoole.[FlagForOrder],
[PLUState],
[PLUStateDescription],
[ProductOutline],
[Season],
StockPoole.[LastSold],
StockSoton.[LastSold],
StockPoole.[TotalStock],
StockSoton.[TotalStock] 

ORDER BY $Sort $ASC

johanafm

You should not use both distinct and group by in the same query. You should use group by if you need to use aggregate functions. If you have a group by clause, each and every selected field needs to either be part of the group by clause or part of an aggregate function. MySQL does not require this last part, but results may/will be unexepected.

Examples

Let's say you have a table game with person_id, tournament_id and points. person_id matches one id in person table, tournament_id matches one tournament id and points is 3 for win, 1 for tie, 0 for loss. That is, this table has one entry per game and person.

-- Showing number of points per person and tournament
SELECT person_id, tournament_id, COUNT(points) AS total_points
FROM game
GROUP BY person_id, tournament_id
ORDER BY tournament_id ASC, total_points DESC

-- Showing number of points total over all tournaments
-- This requires removing tournament_id from select and group by
SELECT person_id, COUNT(points) AS total_points
FROM game
GROUP BY person_id
ORDER BY total_points DESC

-- In mysql you could retain tournament_id among select fields without error
-- but what would it show? Wether it includes tournament_id 1, 2, 3 or anything
-- else for a given person doesn't matter since the query produces the total
-- number of points for any person, regardless of tournament
SELECT person_id, tournament_id, COUNT(points) AS total_points
FROM game
GROUP BY person_id
ORDER BY total_points DESC

Now, I've no idea what would happen if you have DISTINCT with the last query. But you should not have tournament_id in the last query to begin with, using mysql or not. And you also shouldn't have distinct with any of the above queries regardless of database. So pondering what a composition of two things that should not be done would look like is pointless.

However, I believe the reason that you see duplicates is that you have group by on fields that are not selected.
Example

table t
a    b
---------
1   2
1   1

SELECT a FROM t GROUP BY a, b

-- result
1
1

You have selected two rows containing (1, 1) and (1, 2), but you only select the first digit, which is 1 in both cases. However, you group by both fields, which means that you have one set group by (1, 1) showing 1 and one set grouped by (1, 2) showing 1.

Rampage-Obidos

I have removed all of the Group By lines 1 at a time, the SQL error that comes up tells me I need the ones that are left.

I have also removed the Distinct clause as you kinda suggested, and noticed no change.

I have removed all Select options that aren't called.

My results list has now gone from 73 to 105, and even more duplicates show.

Oh and this is an ODBC query on an MSSQL data source, if that makes any difference.

	SELECT
	HistoryPoole.[PLU],
	HistoryPoole.[HistoryDate],
	HistorySoton.[HistoryDate],
	Sum(HistorySoton.[Sales]) AS [SotonSales],
	Sum(HistoryPoole.[Sales]) AS [PooleSales],
	(Sum(HistorySoton.[Sales]) + Sum(HistoryPoole.[Sales])) AS [TotalSales],
	((Sum(HistoryPoole.[Sales]) + Sum(HistorySoton.[Sales])) * [SalePrice1]) AS [SalesValue],
	StockPoole.[TotalStock] AS [PooleStock],
	StockSoton.[TotalStock] AS [SotonStock],
	(StockPoole.[TotalStock]+StockSoton.[TotalStock]) AS [TotalStock],
	[SupplierID],
	[SupplierCode],
	[ProductDescription],
	StockPoole.[FlagForOrder],
	[PLUState],
	[PLUStateDescription],
	[ProductOutline],
	StockPoole.[LastSold] AS [PooleLastSold],
	StockSoton.[LastSold] AS [SotonLastSold]

FROM
Eskimo.dbo.PLUHistory AS [HistoryPoole]
LEFT JOIN Eskimo.dbo.PLU AS [PLUPoole] ON PLUPoole.[PLU] = HistoryPoole.[PLU] 
LEFT JOIN Eskimo.dbo.Stock AS [StockPoole] ON PLUPoole.[PLU] = StockPoole.[StockID] 
LEFT JOIN Eskimo.dbo.PLUState AS [StatePoole] ON PLUPoole.[PLUState] = StatePoole.[PLUStateID] 
LEFT JOIN EskimoSoton.dbo.[Stock] AS [StockSoton]  ON PLUPoole.PLU = StockSoton.[StockID] 
LEFT JOIN EskimoSoton.dbo.[PLUHistory] AS [HistorySoton] ON HistoryPoole.[PLU] = HistorySoton.[PLU]  

WHERE
CONVERT(CHAR(10),HistoryPoole.[HistoryDate],120)>='$SoldIn' AND
CONVERT(CHAR(10),HistorySoton.[HistoryDate],120)>='$SoldIn' AND
HistoryPoole.[StoreNumber]='001' AND
HistorySoton.[StoreNumber]='004' AND
(HistoryPoole.[Sales]>'0' OR HistorySoton.[Sales]>'0') AND
[SupplierID]='$Supplier' AND
StockPoole.[StoreID]='001' AND
StockSoton.[StoreID]='004'
$Seasonlist$PLUState 

GROUP BY
HistoryPoole.[PLU],
HistoryPoole.[HistoryDate],
HistorySoton.[HistoryDate],
StockPoole.[TotalStock],
StockSoton.[TotalStock],
[SupplierID],
[SupplierCode],
[SalePrice1],
[ProductDescription],
StockPoole.[FlagForOrder],
[PLUState],
[PLUStateDescription],
[ProductOutline],
StockPoole.[LastSold],
StockSoton.[LastSold] 

ORDER BY $Sort $ASC

johanafm

Looking at your selected fields (not part of an aggregate function) and comparing these to your group by clause still tells me you have 3 fields that aren't selected but are part of the group by clause
edit: 1 field

GROUP BY
-- snip --
    [SalePrice1],
-- snip --

Also, if you select a bunch of fields (not part of an aggregate function) but only actually display a subset of those, this would cause the same thing as grouping by fields that you do not select.
So also make sure that you acutally echo all fields that you select.

Rampage-Obidos

Removing [SalePrice1] returns the error "Column 'Eskimo.dbo.PLU.SalePrice1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

I don't know which other 2 you are referring to, but as I said, I have removed them all one by one and the above error appears each time.

The below fields are the ones called:

$PLU=$row["PLU"];
$PooleSales=$row["PooleSales"];
$SotonSales=$row["SotonSales"];
$TotalSales=$row["TotalSales"];
$PooleStock=$row["PooleStock"];
$SotonStock=$row["SotonStock"];
$TotalStock=$row["TotalStock"];
$SalesValue=$row["SalesValue"];
$SupplierCode=$row["SupplierCode"];
$CaseQTY=$row["CaseQty"];
$InnerQTY=$row["InnerQty"];
$FlagForOrder=$row["FlagForOrder"];
$PLUState=$row["PLUState"];
$PLUStateDescription=$row["PLUStateDescription"];
$ProductDescription=$row["ProductDescription"];
$ProductOutline=$row["ProductOutline"];
$PooleLastSold=$row["PooleLastSold"];
$SotonLastSold=$row["SotonLastSold"];

johanafm

The two other fields were me being hasty which is why I added "edit: 1 field" (as opposed to 3). And I also missed SalesPrice1 being multiplied with an aggregate function. So yes, as far as I can see, your SQL looks correct.

However, when it comes to echoing out stuff, there still seem to be missing fields. Your SQL statement contains (as per post 3) the following, rearranged to match the order of fields in your last post

    HistoryPoole.[PLU],
    [SupplierCode],

Sum(HistoryPoole.[Sales]) AS [PooleSales],
Sum(HistorySoton.[Sales]) AS [SotonSales],
(Sum(HistorySoton.[Sales]) + Sum(HistoryPoole.[Sales])) AS [TotalSales],

StockPoole.[TotalStock] AS [PooleStock],
StockSoton.[TotalStock] AS [SotonStock],
(StockPoole.[TotalStock]+StockSoton.[TotalStock]) AS [TotalStock],

((Sum(HistoryPoole.[Sales]) + Sum(HistorySoton.[Sales])) * [SalePrice1]) AS [SalesValue],
StockPoole.[FlagForOrder],
StockPoole.[LastSold] AS [PooleLastSold],
StockSoton.[LastSold] AS [SotonLastSold] 

missing: CaseQty, InnerQty

[PLUState],
[PLUStateDescription],
[ProductDescription],
[ProductOutline],

Extra:
HistoryPoole.[HistoryDate],
HistorySoton.[HistoryDate],
[SupplierID],

Either way, your previous post has 18 fields and your sql statement has 19 fields (where the latter lacks two fields from your previous post while it contains 3 additional fields).
Your table (as per the image linked from your first post) shows 15 columns. Which 3 (or 4?) fields are not shown there? One or more of the fields never echoed might (should) contain the data that makes each row unique.

Rampage-Obidos

I noticed that just after I posted and removed them.

I have double checked the rest of the selected fields against those grouped and those used and they all match up. Every field I call is used (there is hover over text you can't see in the image).

I am going to go back to my basic query I started with (before attempting to join 2 databases) and try again, if I make it work I will post the query here. Hopefully then I'll be able to spot the glaring error.

Thank you very much for your help and explanation, I now understand a bit more about how things work 🙂

Rampage-Obidos

After much trial and error, I have managed to remove the duplicate listing, however I no longer have the results I have been looking for.

The only way I can make it work is to remove the "LEFT JOIN EskimoSoton.dbo.[PLUHistory] AS [HistorySoton] ON HistoryPoole.[PLU] = HistorySoton.[PLU]" which seems to create the problem.

Thank you again for all your help.