0

I have gone through dozens of questions on this site and others, but am still having issues adding a GROUP BY to my code. One came close, but not sure I fully understand it: SQL/mysql - Select distinct/UNIQUE but return all columns?

I'm working on a program that tracks tracking numbers for orders shipped, but need each sales order only listed once as I need to assign a status to each order. PackageReferenceNo1 contains the sales order numbers, but I only need one tracking number for each order. The following code pulls all tracking numbers and their sales orders. My thought was if I could SELECT DISTINCT, or GROUP BY, I could resolve this issue but haven't had any luck with either option.

My working code is:

SELECT tblImportUPS.ManifestDate,
    tblImportUPS.TrackingNumber,
    tblImportUPS.PackageReferenceNo1,
    tblImportUPS.PackageReferenceNo2,
    tblImportUPS.STATUS,
    tblTrackingLinks.Notes,
    tblImportUPS.ShipToName,
    tblTrackingLinks.PGId,
    tblTrackingLinks.BBReleased,
    tblTrackingLinks.BBReleaseDate,
    tblTrackingLinks.SRFNumber,
    tblImportUPS.ShipToCity,
    tblImportUPS.[ShipToState/Province],
    tblImportUPS.ShipToCountry,
    tblImportUPS.ShipperName,
    tblImportUPS.NumberofPieces,
    tblImportUPS.Weight,
    tblImportUPS.ScheduledDelivery,
    tblImportUPS.DateDelivered
FROM tblTrackingLinks
INNER JOIN tblImportUPS ON (tblTrackingLinks.TrackingNumber = tblImportUPS.TrackingNumber)
    AND (tblTrackingLinks.TrackingNumber = tblImportUPS.TrackingNumber)
WHERE (((tblImportUPS.PackageReferenceNo1) LIKE "*56*")
        AND ((tblImportUPS.STATUS) <> "Void"))
ORDER BY tblImportUPS.ManifestDate DESC;

This works fine, but repeats multiple tracking numbers for each order. I try to add GROUP BY tblImportUPS.PackageReferenceNo1 before the ORDER BY line and receive an aggregate error.

Can anyone advise the correct way to proceed and why? I'd prefer to understand and not just receive the solution.

Parfait
  • 104,375
  • 17
  • 94
  • 125
Inzzane
  • 1
  • 2
  • 3
    Use `SELECT DISTINCT` to remove duplicate rows from the result. Use `GROUP BY` when you also have aggregate functions. – jarlh Mar 08 '18 at 13:37
  • 2
    Don't read about MySQL if you want GROUP BY help... – jarlh Mar 08 '18 at 13:39
  • 1
    Further to @jarlh's comment, distinct will select each unique row across all columns, so if one fo the columns has a value that differs you'll still get repetition. – JohnHC Mar 08 '18 at 13:39
  • Can an order have multiple tracking numbers? If yes, how are you determining which should be returned? Sample data would be helpful. – Wes H Mar 08 '18 at 13:56
  • What field is the Primary Key for each table? Why do you have the join condition twice? – Wes H Mar 08 '18 at 14:02
  • Correct. i can not use DISTINCT, as 'PackageReference2' values will be different for each shipment (as that field contains serial numbers) as well as TrackingNumber which is different for every package. PackageReference1 contains the sales order it is shipping on, and I just need one package from the sales order listed. Which package is not of any concern, as long as it is just one package. – Inzzane Mar 08 '18 at 15:38
  • For PK's, in tblImportUPS the field is ID, for tblTrackingLinks the filed is TrackingNumber – Inzzane Mar 08 '18 at 15:39
  • To reiterate Wes H, why is the join condition repeated and provide sample raw data and desired output. – June7 Mar 08 '18 at 18:35

0 Answers0