Group by MIN value in SQL Server pivot table query

Question

I have looked through some similar questions but still can't figure this out.

My query:

SELECT * 
FROM  
    (SELECT 
         Sites.ID AS SiteID, 
         MIN(DateInspectionDue) AS DateDue, 
         ItemType, SiteItems.ID AS SiteItemID
     FROM 
         Clients
     INNER JOIN 
         Sites ON Clients.ID = Sites.ClientID
     INNER JOIN 
         SiteItems ON Sites.ID = SiteItems.SiteID
     INNER JOIN 
         Items ON SiteItems.ItemID = Items.ID
     GROUP BY 
         Sites.ID, ItemType, SiteItems.ID
     HAVING 
         MIN(SiteItems.DateInspectionDue) < '2023-09-01') t
PIVOT
    (COUNT(SiteItemID)
     FOR ItemType IN (A, B, C, D)
    ) pivot_table
GROUP BY 
    DateDue, SiteID, A, B, C, D
ORDER BY 
    SiteID

Here is my example data:

And here is my results from the query shown:

I only want one row for each site, showing the minimum due date from all items at that site.

This means the expected result for site 3 is one row, like this:

SiteID  | DateDue    |  A   |   B   |   C   |   D
--------+------------+------+-------+-------+-----
   3    | 2023-08-11 |  2   |   0   |   0   |   1

There is a SQLFiddle here: http://sqlfiddle.com/#!18/1ef37/1

How can I do this?

Answer 1

You seem to have most of it fixed, just need to change your group by a bit?

SELECT siteid, min(datedue), sum(a), sum(b), sum(c), sum(d)  -- changed here
FROM (
  SELECT Sites.ID AS SiteID, MIN(DateInspectionDue) AS DateDue, ItemType, SiteItems.ID AS SiteItemID
  FROM Clients
  INNER JOIN Sites ON Clients.ID = Sites.ClientID
  INNER JOIN SiteItems ON Sites.ID = SiteItems.SiteID
  INNER JOIN Items ON SiteItems.ItemID = Items.ID
  GROUP BY Sites.ID, ItemType, SiteItems.ID
  HAVING MIN(SiteItems.DateInspectionDue) < '2023-09-01'
) t
PIVOT(
  COUNT(SiteItemID)
  FOR ItemType IN (A, B, C, D)
) pivot_table
GROUP BY  SiteID -- Removed a lot of grouping
ORDER BY SiteID