Angel02
asked on
Multiple Sum columns in different tables
I have the following two tables
Table: Daily
Product Date1 OrderNumber Boxes
A 01/05/2015 123 5
A 01/06/2015 234 1
A 01/07/2015 235 1
C 01/15/2015 345 2
C 01/16/2015 456 4
D 02/01/2015 567 7
D 01/02/2015 678 3
D 01/02/2015 689 3
Table: TBF
Product Week Year Boxes Period
A 1 2015 4 1
A 1 2015 4 2
C 2 2015 1 1
C 2 2015 3 2
D 5 2015 5 1
D 5 2015 10 2
I need the sum the boxes in Daily grouping by year, weeknumber and product. I then need to sum the boxes in FC for the same Week, year and product.
This is the query I have but its giving the wrong number for Sum(Boxes) in TBF
SELECT Daily.PRODUCT, Year(Daily.DATE1) AS DYear, Val(Format(Daily.DATE1,"ww ",1,2)) AS DWeek, Sum(Daily.[BOXES]) AS DBOXES,
Sum(TBF.BOXES) AS SumOfBOXES
FROM Daily LEFT JOIN TBF
ON (((TBF.PRODUCT)=[Daily].[P roduct]) AND ((TBF.YEAR)=Year([Daily].[ DATE1])) AND ((TBF.WEEK)=Val(Format([Da ily].[DATE 1],"ww",1, 2))))
GROUP BY Daily.PRODUCT, Year(Daily.DATE1), Val(Format(Daily.DATE1,"ww ",1,2));
Table: Daily
Product Date1 OrderNumber Boxes
A 01/05/2015 123 5
A 01/06/2015 234 1
A 01/07/2015 235 1
C 01/15/2015 345 2
C 01/16/2015 456 4
D 02/01/2015 567 7
D 01/02/2015 678 3
D 01/02/2015 689 3
Table: TBF
Product Week Year Boxes Period
A 1 2015 4 1
A 1 2015 4 2
C 2 2015 1 1
C 2 2015 3 2
D 5 2015 5 1
D 5 2015 10 2
I need the sum the boxes in Daily grouping by year, weeknumber and product. I then need to sum the boxes in FC for the same Week, year and product.
This is the query I have but its giving the wrong number for Sum(Boxes) in TBF
SELECT Daily.PRODUCT, Year(Daily.DATE1) AS DYear, Val(Format(Daily.DATE1,"ww
Sum(TBF.BOXES) AS SumOfBOXES
FROM Daily LEFT JOIN TBF
ON (((TBF.PRODUCT)=[Daily].[P
GROUP BY Daily.PRODUCT, Year(Daily.DATE1), Val(Format(Daily.DATE1,"ww
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it. This is how I did it. Please let me know if any improvements.
Select TDaily.product, TDaily.DYear, TDaily.DWeek,TDaily.DBOXES , FC.FCBoxes from (SELECT Daily.product, YEAR(Daily.DATE1) AS DYear, Val(FORMAT(Daily.DATE1, "ww", 1, 2)) AS DWeek, SUM(Daily.BOXES) AS DBOXES
FROM Daily
GROUP BY Daily.product, YEAR(Daily.DATE1), Val(FORMAT(Daily.DATE1, "ww", 1, 2))) As TDaily
LEFT JOIN (SELECT product, [year], [week], SUM(BOXES) As FCBoxes
FROM TBF
Group by product,[Year], [Week]) As FC
ON FC.product = TDaily.product
AND FC.[Year] = (TDaily.DYEAR)
AND FC.[Week] = TDaily.DWeek
Select TDaily.product, TDaily.DYear, TDaily.DWeek,TDaily.DBOXES
FROM Daily
GROUP BY Daily.product, YEAR(Daily.DATE1), Val(FORMAT(Daily.DATE1, "ww", 1, 2))) As TDaily
LEFT JOIN (SELECT product, [year], [week], SUM(BOXES) As FCBoxes
FROM TBF
Group by product,[Year], [Week]) As FC
ON FC.product = TDaily.product
AND FC.[Year] = (TDaily.DYEAR)
AND FC.[Week] = TDaily.DWeek
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The query wouldn't work for me because table TBF does not have all the products that Daily has and also its calculating the SumTBFBoxes wrong.
Ill however use ISO_WeekYearNumber in my query. Thanks for the idea.
Ill however use ISO_WeekYearNumber in my query. Thanks for the idea.
> .. table TBF does not have all the products that Daily has ..
OK, couldn't see that from the example data. Then you need an outer join.
/gustav
OK, couldn't see that from the example data. Then you need an outer join.
/gustav
Regarding the data
> if table Daily can have a product X that isn't in TBF; can TBF have a product Y that is not in Daily?
> could TBF have year & week information that isn't in Daily?
------------
@gustav
> if table Daily can have a product X that isn't in TBF; can TBF have a product Y that is not in Daily?
> could TBF have year & week information that isn't in Daily?
------------
@gustav
>>" it is much simpler to join via a where clause."
Oh, goodness, I so disagree. Mixing filtering predicates with join logic can be ever so confusing but ANSI join syntax clearly differentiates them.
also:
HAVING is usually performed after the GROUP BY, did you mean to use WHERE? could just be an "air code error" :)
Oh, goodness, I so disagree. Mixing filtering predicates with join logic can be ever so confusing but ANSI join syntax clearly differentiates them.
also:
HAVING is usually performed after the GROUP BY, did you mean to use WHERE? could just be an "air code error" :)
Yes.
/gustav
/gustav
ASKER
Can you please give me a sample of join?