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?

siggemannen · Accepted Answer · 2023-08-29 11:30:04Z

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

answered Aug 29, 2023 at 11:30

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arulkumar Over a year ago

Instead of SUM of A to D, MIN will get the expected result. SELECT siteid, MIN(datedue), MIN(a), MIN(b), MIN(c), MIN(d) . Fiddle: sqlfiddle.com/#!18/1ef37/12

Sparrowhawk Over a year ago

Perfect, thanks! And SUM (as in the answer) not MIN was required for the results I need.

Collectives™ on Stack Overflow

Group by MIN value in SQL Server pivot table query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related