• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

SQL Group

Hello,
can you please help,
I use below code to get Driver payments from SQL 2008
Can you please help me group them in a way that I only get one Total number per Driver.
Please see attached.

Select PickupDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.PickupDriver)  AS [Driver Name],PUDriverCommission,
DeliveryDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.DeliveryDriver)  AS [Driver Name],DELDriverCommission,
Driver3 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.Driver3)  AS [Driver Name],Driver3Commission,
Driver4 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.Driver4)  AS [Driver Name],Driver4Commission,
Driver5 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.Driver5)  AS [Driver Name],Driver5Commission
From ActiveOrders AO
UNION ALL
Select PickupDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.PickupDriver)  AS [Driver Name],PUDriverCommission,
DeliveryDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.DeliveryDriver)  AS [Driver Name],DELDriverCommission,
Driver3 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.Driver3)  AS [Driver Name],Driver3Commission,
Driver4 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.Driver4)  AS [Driver Name],Driver4Commission,
Driver5 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.Driver5)  AS [Driver Name],Driver5Commission
From FinalizedOrders FO
where orderno in (select orderno from FinalizedOrdersDriverSettlements where Driver1SettlementNo is null)

Thank you for your help,
Sample.xlsx
0
W.E.B
Asked:
W.E.B
  • 2
  • 2
1 Solution
 
jayakrishnabhCommented:
;with cte(
select [Driver#] as Driver, [Driver Name] as Dname, [PUDriverCommission] as Commission From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [DELDriverCommission] as Commission  From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [Driver3Commission] as Commission From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [Driver4Commission] as Commission From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [Driver5Commission] as Commission From YourTable where [Driver#] is not null
)
select Driver, SUM(ISNULL(Commission,0))
from cte
group by Driver
0
 
W.E.BAuthor Commented:
Hello,
thank you for the help,

I'm having a problem applying your suggestion,
how do I use this on 2 tables.
I have Activeorders, FinalizedOrders

I appreciate if you can show me the whole code, this way I can learn how to.

thanks
0
 
jayakrishnabhCommented:
;with cte1 as (
select [Driver#] as Driver, [Driver Name] as Dname, [PUDriverCommission] as Commission From Activeorders where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [DELDriverCommission] as Commission  From FinalizedOrders where [Driver#] is not null
)
select Driver, SUM(ISNULL(Commission,0))
from cte1
group by Driver
0
 
W.E.BAuthor Commented:
Thank you very much.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now