Dryden Long Dryden Long - 1 month ago 15
SQL Question

GROUP BY with SUM and Latest Date

I have the following query

SELECT
TableA.MaterialCode,
TableB.LotNumber,
SUM(TableB.Quantity) AS Quantity,
CONVERT(varchar(25), TableC.IssueDate, 121) AS IssueDate
FROM TableA
INNER JOIN
TableB ON TableA.PONumber = TableB.PONumber
INNER JOIN
TableC ON TableA.PONumber = TableC.PONumber
WHERE TableC.VendorID = 'VendorID'
AND TableC.IssueDate > '10/25/2016 9:47:53 AM'
GROUP BY MaterialCode, LotNumber, IssueDate
ORDER BY TableC.IssueDate DESC


Which returns a dataset like so:

╔══════════════╦═══════════╦══════════╦═════════════════════════╗
║ MaterialCode ║ LotNumber ║ Quantity ║ IssueDate ║
╠══════════════╬═══════════╬══════════╬═════════════════════════╣
║ 12345 ║ 3451972 ║ 55 ║ 2016-10-25 10:48:13.210 ║
║ 12346 ║ 3436055 ║ 32 ║ 2016-10-25 09:57:26.693 ║
║ 12346 ║ 3436055 ║ 224 ║ 2016-10-25 09:47:54.077 ║
╚══════════════╩═══════════╩══════════╩═════════════════════════╝


I would like to expand on this to instead of grouping by the IssueDate, to just get the latest date in the group. I have tried the code below, but it throws the error
...because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT
TableA.MaterialCode,
TableB.LotNumber,
SUM(TableB.Quantity) AS Quantity,
MAX(CONVERT(varchar(25), TableC.IssueDate, 121)) AS IssueDate
FROM TableA
INNER JOIN
TableB ON TableA.PONumber = TableB.PONumber
INNER JOIN
TableC ON TableA.PONumber = TableC.PONumber
WHERE TableC.VendorID = 'VendorID'
AND TableC.IssueDate > '10/25/2016 9:47:53 AM'
GROUP BY MaterialCode, LotNumber
ORDER BY TableC.IssueDate DESC


Here is the output I was hoping to get with above query:

╔══════════════╦═══════════╦══════════╦═════════════════════════╗
║ MaterialCode ║ LotNumber ║ Quantity ║ IssueDate ║
╠══════════════╬═══════════╬══════════╬═════════════════════════╣
║ 12345 ║ 3451972 ║ 55 ║ 2016-10-25 10:48:13.210 ║
║ 12346 ║ 3436055 ║ 256 ║ 2016-10-25 09:57:26.693 ║
╚══════════════╩═══════════╩══════════╩═════════════════════════╝


How can I alter my query to achieve the above dataset?

Answer

Your query should be

SELECT 
     a.MaterialCode, 
     b.LotNumber, 
     SUM(b.Quantity) AS Quantity, 
     CONVERT(varchar(25), MAX(c.IssueDate), 121) AS MaxIssueDate
FROM TableA a
INNER JOIN
    TableB b ON a.PONumber = b.PONumber 
INNER JOIN
    TableC c ON a.PONumber = c.PONumber
WHERE c.VendorID = 'VendorID'
    AND c.IssueDate > '10/25/2016  9:47:53 AM'
GROUP BY a.MaterialCode, b.LotNumber
ORDER BY MaxIssueDate DESC;

And as comment above, if you want to include a.PONumber in result set, then add it in GROUP BY clause too.

The SELECT MAX(CONVERT(varchar(25), TableC.IssueDate, 121)) AS IssueDate is not an issue, the problem here is your ORDER BY clause.

You could use ORDER BY MAX(CONVERT(varchar(25), TableC.IssueDate, 121)), but it's better to use just ORDER BY MaxIssueDate like above code.

Comments