Solved

SQL Group

Posted on 2014-03-27
4
260 Views
Last Modified: 2014-03-27
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
Comment
Question by:W.E.B
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:jayakrishnabh
ID: 39959009
;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
 

Author Comment

by:W.E.B
ID: 39959047
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
 
LVL 5

Accepted Solution

by:
jayakrishnabh earned 500 total points
ID: 39959063
;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
 

Author Closing Comment

by:W.E.B
ID: 39959169
Thank you very much.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question