SQL Server - Select multiple Columns Group By 1 Column

Question

I have looked at many examples of selecting many columns but only grouping by 1 column and mine seem to give me duplicate results. See below I would like to select all the columns in my table but would like to only GROUP BY VehicleId. On the screenshot you'll see that the results are actually not grouped by VehicleId.

Any idea on what I am doing wrong?

Try 1:

SELECT 
    h.*,

    TotalFines = 1,
    TotalIncidents = 1,
    TotalVehicleAllocations = 1,
    TotalVehicleConditions = 1,
    TotalMileageApplications = 1
FROM
(
    SELECT h1.VehicleId FROM [dbo].[VehicleHistory] h1 GROUP BY h1.VehicleId
) GroupedList
INNER JOIN [dbo].[VehicleHistory] h ON GroupedList.VehicleId=h.VehicleId
ORDER BY
    h.VehicleId;

Try 2:

SELECT t1.* FROM VehicleHistory t1
INNER JOIN
(
    SELECT VehicleId FROM VehicleHistory GROUP BY VehicleId
)   t2
ON t1.VehicleId=t2.VehicleId

Both queries produce the same results with duplicate rows for each VehicleId as per below:

Here's my expected results below. The results are a query produced by Entity Framework. But I would like to rewrite the linq query into T-SQL:

@forpas, I have added how the results should look like.. I am trying to rewrite an Entity Framework Linq Query into a T-SQL query... — Morgs
– Morgs, Commented Jun 7, 2019 at 9:35

Michał Turczyn · Accepted Answer · 2019-06-07 09:35:59Z

1

This is because you are gtouping in subquery (which would be same as SELECT DISTINCT h1.VehicleId FROM [dbo].[VehicleHistory] h1):

SELECT h1.VehicleId FROM [dbo].[VehicleHistory] h1 GROUP BY h1.VehicleId

and then you are joining in on that column, which can cause duplicates to occur (you have duplicate IDs in VehicleHistory).

All you need to do is:

SELECT VehicleId,
       MAX(DateUpdated) DateUpdated, --or other aggregate function
       --rest of your columns in appropriate aggreagte functions
FROM VehicleHistory
GROUP BY VehicleId

answered Jun 7, 2019 at 9:35

Michał Turczyn

41.3k18 gold badges58 silver badges88 bronze badges

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

2 Comments

Morgs Over a year ago

Yes that produces expected results, but is that the best approach, adding MAX on all other columns?

Michał Turczyn Over a year ago

@Morgs You can use whatever you need, that depends on you and cannot be thought as "best approach"", because it depends.

Collectives™ on Stack Overflow

SQL Server - Select multiple Columns Group By 1 Column

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