Solved

SQL Group

Posted on 2014-03-27
4
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 79
Present Absent from working date rage 11 49
Parse this column 6 35
Can a Trigger trigger a Trigger? 4 21
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, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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