CODEWITHSUNDEEP
sqlsql-serverjoingroup-bydistinct
David White
David White

Reputation: 141

Group by aggregate

This is my first touch with SQL Server and literally I have no idea what I'm doing.
Until today I only used MySQL, but lets cut to the chase.
I have a query which have joins.

SELECT locations.locationId, locations.code, locations.description,
       machines.RoutingCode, machines.FaNo, machines.nameBg 
FROM scanlog 
LEFT JOIN  locations ON locations.Barcode = scanlog.LineBarcode 
LEFT JOIN machines ON machines.barcode = scanlog.MachineBarcode

The problem: I want to group the rows on machines.RoutingCode and locations.code.
I mean like in MySQL. If I have 20 entries with the same routingCode and same code and it will return to me only one row. So is there any way to do it here, or I should try something with DISTINCT?

Upvotes: 2

Views: 64

Answers (1)

Devart
Devart

Reputation: 122002

Try something like this -

SELECT
      locationId = MAX(l.locationId)
    , l.Code
    , [description] = MAX(l.[description])
    , m.RoutingCode
    , FaNo = MAX(m.FaNo)
    , nameBg = MAX(m.nameBg)
FROM dbo.scanlog s
LEFT JOIN dbo.locations l ON l.BarCode = s.LineBarcode
LEFT JOIN dbo.machines m ON m.BarCode = s.MachineBarcode
GROUP BY 
       l.Code
     , m.RoutingCode

Upvotes: 2

Related Questions