SQL join, sum

Question

Hi

I have 3 tables

1. bl_main containing two columns bl_id and bl_area

bl_id is unique here.

1. bl_details containing two columns bl_id, name

bl_id is uniue again.

1. bl_data containing 4 columns bl_id, month, paper_tons, bottles_tons

bl_id is not unique here. There will be multiple rows of same bu_id.

I am trying to retrieve data in the following way

bl_id | name | bl_area | sum(paper_tons) | sum (bottles_tons) | paper_tons | bottles_tons

sum(paper_tons) should return the sum of all the paper tons for the same bu_id like Jan to December.

Also all the bu_ids from bl_main must be retrieved even though no corresponding bu_id is present in bl_data. In this case the sum should be shown as 0.

I have not written complex sql queries before, please help me out!

Thanks in advance!

Answer 1

Untested but I think that should do it (For MS SQL Server)
Edited for max month

SELECT 

    bl_main.bl_id,
    name,
    bl_area,
    sums.SummedPaper, 
    sums.SummedBottles,
    paper_tons,
    bottles_tons
FROM 
    bl_main

JOIN bl_details ON 
    bl_main.bl_id= bl_details.bl_id
JOIN bl_data ON
    bl_data.bl_id= bl_main.bl_id
JOIN (
    SELECT bl_id, 
     SUM(Paper_tons) As SummedPaper, 
 SUM(bottle_tons) As SummedBottles 
FROM
 bl_data
    WHERE Month = (SELECT MAX(Month) FROM bl_data) 
GROUP by bl_id) sums ON 
    sums.bl_id = bl_main.bl_id

Answer 2

I guess you have MS SQL Server. Try this (not tested yet).

SELECT 
    bl_main.bl_id, 
    bl_main.bl_area,
    bl_id.[name], 
    sum(bl_data.paper_tons) OVER (PARTITION BY bl_main.bl_id),  
    sum (bl_data.bottles_tons) OVER (PARTITION BY bl_main.bl_id), 
    paper_tons , 
    bottles_tons
FROM  bl_main 
    Right join bl_id on bl_main.bl_id = bl_id.bl_id
    right join bl_data on bl_main.bl_id = bl_data.bl_id

Answer 3

well, thanks for your help every one. I have pretty much got it now and can display all the bu_ids from bl_main.

SELECT
bl.bl_id,bl_googlegis.name,round(bl.area_ocup,2), sums.SummedPaper,sums.SummedBottles,
bl_recycling.tons_paper,bl_recycling.tons_cans_bottles, bl_recycling.period_month FROM bl JOIN bl_googlegis ON bl.bl_id= bl_googlegis.bl_id left outer JOIN bl_recycling ON
bl_recycling.bl_id= bl.bl_id
left outer JOIN ( SELECT bl_id, SUM(tons_paper) As SummedPaper, SUM(tons_cans_bottles) As SummedBottles FROM bl_recycling GROUP by bl_id) sums ON
sums.bl_id = bl.bl_id

But it displays each bu_id multiple times(as many months present). I just want to display the bu_id with the max month, some thing like this .......where period_month= (select Max(period_month) from bl_recycling) . But dont know how to incorporate in this.

Thanks in advance. Appreciate it!