0

I'm adding data to a SQL database (thousands of rows), aggregating the columns, and then using the processed data to build invoices (tens of rows per invoice).

I GROUP BY three columns:

GROUP BY [KjøretøyID], [LøyvehaverID], [Ordrenummer]

[Ordrenummer] is a distinct, unique number for each row. I need to group by [KjøretøyID] (vehicle) and [LøyvehaverID] (company), which means that each [LøyvehaverID] end up with 2-10 rows each. But since [Ordrenummer] is part of the GROUP BY as well, they end up with several hundreds each (non-aggregated rows).

So, in my C# code, my question is: Is there any way I can skip [Ordrenummer] when I am nesting foreach loops?

IEnumerable<IGrouping<String, PaidTrip>> tripsGroupedByCompany = paidTrips.GroupBy(pt => pt.LicenseHolderID);

foreach (IGrouping<String, PaidTrip> companyGroup in tripsGroupedByCompany) {

    // code

    var groupedByVehicle = companyGroup.GroupBy(t => t.VehicleID);
    
    foreach (IGrouping<String, PaidTrip> vehicleGroup in groupedByVehicle) {
    
    // code

        foreach (PaidTrip trip in vehicleGroup) {

        // this is where there's an overflow. I'm supposed to have 2-10 rows, but get hundreds (the non-aggregated total)
        
        }
    }
}
Ole M
  • 317
  • 1
  • 17
  • Why are you grouping by the order number if it's unique for every row? You'll just end up with the same number of rows as if you hadn't used `GROUP BY` at all... – Richard Deeming Oct 14 '21 at 09:46
  • If that's the case then I admit that's a silly thing to do. I'm just trying to figure out if it's possible to filter by the non-aggregated order numbers. If I don't `GROUP BY` I have to aggregate, and no aggregate function makes sense. I just want to skip over any row that has a duplicate order number. – Ole M Oct 14 '21 at 11:48
  • You can do this in SQL see https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group – Charlieface Oct 14 '21 at 13:23

0 Answers0