SQL SUM and divide linked tables

Question

I have the following tables:

create table Cars
(
  CarID int,
  CarType varchar(50),
  PlateNo varchar(20),
  CostCenter varchar(50),
  
);

insert into Cars (CarID, CarType, PlateNo, CostCenter) values 
(1,'Coupe','BC18341','CALIFORNIA'),
(2,'Hatchback','AU14974','DAKOTA'),
(3,'Hatchback','BC49207','NYC'),
(4,'SUV','AU10299','FLORIDA'),
(5,'Coupe','AU32703','NYC'),
(6,'Coupe','BC51719','CALIFORNIA'),
(7,'Hatchback','AU30325','IDAHO'),
(8,'SUV','BC52018','CALIFORNIA');

create table Invoices
(
  InvoiceID int,
  InvoiceDate date,
  CostCenterAssigned bit,
  InvoiceValue money 
);

insert into Invoices (InvoiceID, InvoiceDate, CostCenterAssigned, InvoiceValue) values 
(1, '2021-01-02', 0, 978.32),
(2, '2021-01-15', 1, 168.34),
(3, '2021-02-28', 0, 369.13),
(4, '2021-02-05', 0, 772.81),
(5, '2021-03-18', 1, 469.37),
(6, '2021-03-29', 0, 366.83),
(7, '2021-04-01', 0, 173.48),
(8, '2021-04-19', 1, 267.91);

create table InvoicesCostCenterAllocations
(
  InvoiceID int,
  CarLocation varchar(50)
);

insert into InvoicesCostCenterAllocations (InvoiceID, CarLocation) values 
(2, 'CALIFORNIA'),
(2, 'NYC'),
(5, 'FLORIDA'),
(5, 'NYC'),
(8, 'DAKOTA'),
(8, 'CALIFORNIA'),
(8, 'IDAHO');

How can I calculate the total invoice values allocated to that car based on its cost center?

If the invoice is allocated to cars in specific cost centers, then the CostCenterAssigned column is set to true and the cost centers are listed in the InvoicesCostCenterAllocations table linked to the Invoices table by the InvoiceID column. If there is no cost center allocation (CostCenterAssigned column is false) then the invoice value is divided by the total number of cars and summed up.

The sample data in Fiddle: http://sqlfiddle.com/#!18/9bd18/3

What have you tried so far? Why didn't it work? Did you get an error? Unexpected results? Something else? — Thom A, May 28 '21 at 12:49
You didn't post your query here, rather it was only in the SQL fiddle. Your main issue is that your subquery will return more than one record (provided that you're expecting results from more than one center, and/or a center and some non-assigned cars) — Eli, May 28 '21 at 13:12
I don't think that you want them summed up, as you'll want to have a way to tell the different centers, etc. Try to have a CASE statement in the main query, and the THEN should have a full query, i.e. your logic to have the numbers crunched for the center — Eli, May 28 '21 at 14:51
The connection between Cars and Invoices is based on CostCenter column (this was CarLocation in Cars table, but I renamed it to CostCenter also to make more sense). I modified the script, please see: http://sqlfiddle.com/#!18/3c323b/5 I managed to calculate the invoice value per vehicle, but how can I display it in one query? End result should be this: PlateNumber | CostCenter | InvoiceValuePerVehicle — milo2011, May 29 '21 at 14:00
There's no way to achieve what you want, ie having a line per vehicle, because NONE of you tables have a foreign key to the CAR table (CarID or PlateNo). There's a flaw in your design. Ideally an Invoice (or an invoice line in a sub table) should contain a Vehicle ID. — Thomas G, May 31 '21 at 10:30
please provide desired output as well with a clear explanation — eshirvana, May 31 '21 at 14:26
Without expected results (and we're still missing your attempt I [asked for](https://stackoverflow.com/questions/67739109/sql-sum-and-divide-linked-tables#comment119732539_67739109)) this is difficult to attempt. — Thom A, Jun 01 '21 at 16:24

score 1 · Accepted Answer · answered Jun 01 '21 at 17:17

The data structure here isn't perfect, hence we need some extra code to solve for this. I needed to gather the amount of cars in each location, as well as to allocate the amounts for each invoice, depending on whether or not it was assigned to a location. I broke out the totals for each invoice type so that you can see the components which are being put together, you won't need those in your final result.

;WITH CarsByLocation AS(    
    SELECT  
         CostCenter
        ,COUNT(*) AS Cars
    FROM Cars 
    GROUP BY CostCenter
    UNION ALL
    SELECT  
         ''
        ,COUNT(*) AS Cars
    FROM Cars   
),CostCenterAssignedInvoices AS (
    SELECT 
         InvoicesCostCenterAllocations.CarLocation
        ,SUM(invoicevalue) / CarsByLocation.cars AS InvoiceTotal
    FROM Invoices 
    INNER JOIN InvoicesCostCenterAllocations ON invoices.InvoiceID = InvoicesCostCenterAllocations.InvoiceID
    INNER JOIN CarsByLocation on InvoicesCostCenterAllocations.CarLocation = CarsByLocation.CostCenter
    WHERE CostCenterAssigned = 1  --Not needed, put here for clarification
    GROUP BY InvoicesCostCenterAllocations.CarLocation,CarsByLocation.Cars
),UnassignedInvoices AS (
    SELECT 
         '' AS Carlocation
        ,SUM(invoicevalue)/CarsByLocation.Cars InvoiceTotal
    FROM Invoices 
    INNER JOIN CarsByLocation on CarsByLocation.CostCenter = ''
    WHERE CostCenterAssigned = 0
    group by CarsByLocation.Cars
)
SELECT 
      Cars.*
     ,cca.InvoiceTotal AS AssignedTotal
     ,ui.InvoiceTotal AS UnassignedTotal
     ,cca.InvoiceTotal + ui.InvoiceTotal AS Total
FROM Cars 
LEFT OUTER JOIN CostCenterAssignedInvoices CCA ON Cars.CostCenter = CCA.CarLocation
LEFT OUTER JOIN UnassignedInvoices UI ON UI.Carlocation = ''
ORDER BY 
     Cars.CostCenter
    ,Cars.PlateNo;

Thanks! I did it by using a function, but this is more elegant. — milo2011, Jun 03 '21 at 14:36

SQL SUM and divide linked tables

1 Answers1