Solved

# SQL Aggregation Quandary

Posted on 2012-08-29
829 Views
I beat my head on my desk today trying to figure this out and could not.

I've attached the data structure.  It has the three tables and their data with the expected results from a query.  Here are the requirements:

1. Display 1 record per load
2. Only display the office revenue for the office with the most miles.
3. Aggregate the total office revenue
4. Aggregate miles for the load
5. Aggregate the load by fuel charge, line haul charge, and total load charges.
6. The base criteria would look something like " Where A.Load_ID in (1, 2, 3) "

Thanks in advance for any help.  It is truly appreciated.

Ryan
Data.xlsx
0
Question by:nikotromus11

LVL 12

Expert Comment

B.Miles,
(Select Revenue_Amt from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = B.Trip_Id)  Office_Fuel,
(Select Revenue_Amt from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = B.Trip_Id)  Office_Line_Haul,
(Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id and C.Trip_ID = B.Trip_Id) Total_Office,
(Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id)  Load_Fuel,
(Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id)  Load_Line_Haul,
(Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id) Total_Load
from TableA A
left join (select Load_Id, Invoice_ID, MAX(Office) Office, MAX(Trip_Id) Trip_Id, sum(Miles) Miles from TableB group by Load_Id, Invoice_ID) B
0

Author Comment

I'll try this out tomorrow when I get in front of an SQL engine.  But, I can already see it has problems without looking very deep.  For one thing, why are you worried about max office or max trip?  Shouldn't you be more concerned about max miles, as that is the requirement for displaying office information.  Requirement 2 was completely missed, along with requirement 6.
0

LVL 25

Expert Comment

My take on it ...
``````;with tableA as (
select 1 as Load_ID, 123 as Invoice_ID union all
select 2 as Load_ID, 124 as Invoice_ID union all
select 3 as Load_ID, 125 as Invoice_ID
), tableB as (
select 1 as Load_ID, 123 as Invoice_ID, 100 as Trip_ID, 50 as Miles, 500 as Office union all
select 1 as Load_ID, 123 as Invoice_ID, 101 as Trip_ID, 100 as Miles, 501 as Office union all
select 2 as Load_ID, 124 as Invoice_ID, 102 as Trip_ID, 222 as Miles, 502 as Office union all
select 3 as Load_ID, 125 as Invoice_ID, 103 as Trip_ID, 43 as Miles, 503 as Office union all
select 3 as Load_ID, 125 as Invoice_ID, 104 as Trip_ID, 48 as Miles, 504 as Office
), tableC as (
select 123 as Invoice_ID, 100 as Trip_ID, 'Line_Haul' as Rate_Type, 250 as Revenue_Amt union all
select 123 as Invoice_ID, 100 as Trip_ID, 'Fuel' as Rate_Type, 50 as Revenue_Amt union all
select 123 as Invoice_ID, 101 as Trip_ID, 'Line_Haul' as Rate_Type, 500 as Revenue_Amt union all
select 123 as Invoice_ID, 101 as Trip_ID, 'Fuel' as Rate_Type, 75 as Revenue_Amt union all
select 124 as Invoice_ID, 102 as Trip_ID, 'Line_Haul' as Rate_Type, 350 as Revenue_Amt union all
select 124 as Invoice_ID, 102 as Trip_ID, 'Fuel' as Rate_Type, 13 as Revenue_Amt union all
select 125 as Invoice_ID, 103 as Trip_ID, 'Line_Haul' as Rate_Type, 100 as Revenue_Amt union all
select 125 as Invoice_ID, 103 as Trip_ID, 'Fuel' as Rate_Type, 20 as Revenue_Amt union all
select 125 as Invoice_ID, 104 as Trip_ID, 'Line_Haul' as Rate_Type, 7000 as Revenue_Amt union all
select 125 as Invoice_ID, 104 as Trip_ID, 'Fuel' as Rate_Type, 550 as Revenue_Amt
), tableBview as (
, Office, row_number()over(partition by Load_ID, Invoice_ID order by Miles DESC) OfficeRN
, SUM(Miles)over(partition by Load_ID, Invoice_ID) as Miles
FROM tableB
)
, b.Office, b.Miles
, SUM(CASE WHEN c.Rate_Type = 'Fuel' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Fuel
, SUM(CASE WHEN c.Rate_Type = 'Line_Haul' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Line_Haul
, SUM(CASE WHEN b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Total_Office
, SUM(CASE WHEN c.Rate_Type = 'Fuel' THEN c.Revenue_Amt END) as Load_Fuel
, SUM(CASE WHEN c.Rate_Type = 'Line_Haul' THEN c.Revenue_Amt END) as Load_Line_Haul
FROM tableA A
INNER JOIN tableBview B ON a.Load_ID = b.Load_ID AND a.Invoice_ID = b.Invoice_ID AND b.OfficeRN = 1
INNER JOIN tableC C ON b.Invoice_ID = c.Invoice_ID
, b.Office, b.Miles
``````
NOTE: The first 3 CTE's create my test data ... you need only keep the 4th CTE 'tableBview'.
0

Author Comment

What language is this in?

row_number()over(partition
0

LVL 12

Expert Comment

Alright so here you go

B.Miles,
(Select Revenue_Amt from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = D.Trip_Id)  Office_Fuel,
(Select Revenue_Amt from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = D.Trip_Id)  Office_Line_Haul,
(Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id and C.Trip_ID = D.Trip_Id) Total_Office,
(Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id)  Load_Fuel,
(Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id)  Load_Line_Haul,
(Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id) Total_Load
from TableA A
left join (select Load_Id, Invoice_ID, sum(Miles) Miles, MAX(Miles) MaxMiles
from TableB
left join TableB D on D.Load_Id = A.Load_Id and D.Invoice_ID = A.Invoice_Id and D.Miles = B.MaxMiles

Now each of your condition is covered with this query
0

Author Comment

You actually took the time to add the data to temp tables and run it.  I really appreciate it.

I am working with an Informix database engine.  The only think I see in there that I don't recognize is your example is the " row_number()over(partition " syntax.  I'll have to see if I can find something like this in Informix.

Ryan
0

LVL 25

Expert Comment

Informix ... I need to look into that ... row_number() is a SQL Server, Oracle and DB2 function.
0

LVL 25

Accepted Solution

``````SELECT a.Load_ID, a.Invoice_ID
, b.Office, b.Miles
, SUM(CASE WHEN c.Rate_Type = 'Fuel' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Fuel
, SUM(CASE WHEN c.Rate_Type = 'Line_Haul' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Line_Haul
, SUM(CASE WHEN b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Total_Office
, SUM(CASE WHEN c.Rate_Type = 'Fuel' THEN c.Revenue_Amt END) as Load_Fuel
, SUM(CASE WHEN c.Rate_Type = 'Line_Haul' THEN c.Revenue_Amt END) as Load_Line_Haul
FROM tableA A
INNER JOIN (SELECT Load_ID, Invoice_ID, Trip_ID
, Office, (SELECT SUM(Miles)
FROM tableB y
FROM tableB x
WHERE Office = (SELECT FIRST 1 Office
FROM tableB y
ORDER BY Miles DESC) ) B
INNER JOIN tableC C ON b.Invoice_ID = c.Invoice_ID
, b.Office, b.Miles
``````
Move the CTE into an in-line view as Informix doesn't support them either I think ... but not sure I have the syntax right.  I haven't used Informix much ever and not for many, many years.
0

Author Comment

Unfortunately it's what we use at wal-mart logistics.  I much prefer SQL server myself.  I'll try to work with it tomorrow.  Thanks.
0

Author Comment

Dude - with a little finagling, I got it to work!  Thanks a ton!  I'm accepting the solution!!!!!!
0

Author Closing Comment

Thanks Again!
0

## Featured Post

### Suggested Solutions

Import csv files to MS SQL 5 38
Access Query 4 14
(SQL)How to remove leading zeros 4 26
Microsoft SQL Server, Error: 64 8 33
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…