GROUP BY with multiple fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY with multiple fields

Author

Topic

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2014-06-12 : 19:04:11

I have two issues I'm trying to deal with in my code.

1. I'm trying to group by first and last name, which are in two different columns
2. I'm trying to take an average of pay per miles.

Neither of these is working well. Actually, neither is working at all.

This is the code!

SELECT
OD.DriverID,
(W.FirstName + W.LastName AS 'Driver'),
DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0) AS Date,
DATENAME(dw,O.ReadyTimeFrom) AS DayOfTheWeek,
Count(OD.OrderID) AS 'Total Orders',
SUM(O.Distance) AS OrderMiles,
SUM(O.Price) AS 'Total Revenue',
SUM (OD.PayAmount) AS 'Driver Pay',
CAST (SUM(O.Distance) / Count(OD.OrderID) AS decimal (8,2)) AS 'Avge Miles Per Order',
CAST (SUM(O.Distance) / SUM (OD.DriverPay) AS decimal (8,2)) AS 'Avge Pay Per Mile'

FROM qryOrder AS O
LEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderID
inner join tblWorker AS W ON OD.DriverID = W.WorkerID

Thanks,
Dan

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-06-13 : 04:43:24

you need this

GROUP BY 
OD.DriverID, 
W.FirstName + W.LastName, 
DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0),
DATENAME(dw,O.ReadyTimeFrom)

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2014-06-13 : 12:08:19

Sorry, I forgot to include my existing GROUP BY:

GROUP BY
OD.DriverID,
(W.FirstName + W.LastName),
DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0),
DATENAME(dw,O.ReadyTimeFrom)

When I include the second CAST

CAST (SUM(O.Distance) / SUM(OD.PayAmount) AS decimal (8,2)) AS 'Avge Pay Per Mile'

is when my code crashes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources