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

Left Outer Join & Sum of values

I've got three tables, 1 is a lookup table with a list of Descriptions, the second table is a daily cost table storing the values for the items in the descriptions table and the third is a table for jobs.  They look like this:

BudgetItems:                                      Daily Costs                                               Jobs
ID  int PK                                             ID int PK                                                    ID int PK
Description varchar(50)                     BudgetID int FK -> BudgetItems.ID           JobName varchar(50)
                                                           JobID int FK -> Jobs.ID
                                                           Cost numeric 9,2
                                                           Day smalldatetime

I need a query that will produce a list of ALL budgetItems for a particular Day from DailyCosts even if there is no value for that particular Item from BudgetItems.  

For example:  For a particular day:
Item              DailyCost    TotalAcrossAllDays
Labor            123.25                 1003.32
Steel              500.00                 500.00
Cross Beams   NULL                10,259.36
Footings           NULL                3200.25
Pilings             300.00                6952.36

I need a query that will produce that same list of ALL budgetItems with/without the cost for a particular day with a total value for that particular item.  For example, Labor would be a Item in BudgetItems and there'd be entries in DailyCosts that pertain to Labor.  Need to be able to total the value for Labor at the same time the entire list is produced for that particular day.  So the user should be able to select a day, get the value for the labor entered that day and in the next column have the total labour across all days.

Thanks!
0
delvin
Asked:
delvin
1 Solution
 
Eugene ZCommented:
please check if it gave you for all days:

select
bi.Description,sum(bi. Cost) ---,dc.day,j.JobName
from
BudgetItems bi
  left outer join
 DailyCosts   dc
on
bi.id=dc.BudgetID
inner join
Jobs j
on
dc.id=j.id
--where dc.day=CAST(getdate() AS smalldatetime);
group by bi.Description,dc.day
0
 
Miriam0000Commented:
To get both calculations at once, you will need two aliases to the same table: 1with date limit, one without.  The first alias will return only the records for that day (which then get summed), while the second alias returns all records that then get summed.  Join the two daily costs so you don't get a cartesian product,   SQL would look like

SELECT  Bi.Description as Item,
              Sum (dcDay.Cost) as DailyCost,
              Sum(dcAll.Cost) AS Total AcrossAllDays
FROM    BudgetItems bi
LEFT OUTER JOIN DailyCosts dcAll
ON       dcDay.BudgetID = bi.ID
LEFT OUTER JOIN DailyCosts dcDay
ON       dcDay.BudgetID = bi.ID
AND     dcDay.Day = @DayParameter
AND     dcAll.ID  = dcDay.ID
GROUP BY bi.Description
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Miriam0000 is right, I think, with joining the table twice.
however, joining and summing 2 tables will give incrrect results, you will have to join to pre-aggregated queries:

SELECT  Bi.Description as Item,
              Sum (dcDay.sum_Cost) as DailyCost,
              Sum(dcall.sum_Cost) AS Total AcrossAllDays
FROM    BudgetItems bi
LEFT OUTER JOIN (
  select budgetid
   , sum(cost) sum_cost
  from DailyCosts
  group by budgetid
) dcAll
ON       dcAll.BudgetID = bi.ID
LEFT OUTER JOIN (
  select BudgetID
    sum(cost) sum_cost
   from DailyCosts
   where [Day] = @DayParameter
   group by budgetid
) dcDay
ON       dcDay.BudgetID = bi.ID
AND     dcAll.ID  = dcDay.ID
GROUP BY bi.Description
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LowfatspreadCommented:
use case and a single join

SELECT  Bi.Description as Item,
              Sum (case when convert(char(8),Day,113)=@day thenn Cost else null end) as DailyCost,
              Sum(cost) AS Total AcrossAllDays
FROM    BudgetItems bi
LEFT OUTER JOIN
  from DailyCosts as dc
ON       dc.BudgetID = bi.ID
GROUP BY bi.Description
0
 
delvinAuthor Commented:
EugeneZ - Your proposed solution only returned 1 row with 1 item.
Miriam0000 - I get Multi-part identifier "dcDay.BudgetID" could not be bound
angellll - I get same error message with two more "Invalid column name 'ID'
Lowfatspread - sorry, I couldn't get your solution to work at all.

Any suggestions on working past the error messages?
0
 
Miriam0000Commented:
Sorry about that.  Typo in the SQL

SELECT  Bi.Description as Item,
              Sum (dcDay.Cost) as DailyCost,
              Sum(dcAll.Cost) AS Total AcrossAllDays
FROM    BudgetItems bi
LEFT OUTER JOIN DailyCosts dcAll
ON       dcAll.BudgetID = bi.ID
LEFT OUTER JOIN DailyCosts dcDay
ON       dcDay.BudgetID = bi.ID
AND     dcDay.Day = @DayParameter
AND     dcAll.ID  = dcDay.ID
GROUP BY bi.Description
0
 
Scott PletcherSenior DBACommented:

DECLARE @day DATETIME
-- defaults to current day; change as needed for diff. day
SET @day = CONVERT(CHAR(8), GETDATE(), 112)

SELECT  bi.description AS Item,
    SUM(CASE WHEN dc.day = @day THEN ISNULL(dc.cost, 0) ELSE NULL END) AS DailyCost,
    SUM(ISNULL(dc.cost, 0)) AS TotalAcrossAllDays
FROM budgetItems bi
LEFT OUTER JOIN dailyCosts dc ON dc.BudgetID = bi.ID
GROUP BY bi.description
ORDER BY bi.description
0
 
delvinAuthor Commented:
Miriam0000, your solution was exactly what I was looking for, THANK YOU!

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.

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