tags:

views:

217

answers:

3

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!

A: 

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
John Nolan
Thanks a lot, appreciate it! The problem is, it shows up all the months for each bu_id. I just want unique bu_id with the total sum and the value from the max month (December if present) only. Thanks in advance
Thanks once again. But this only returns the first bu_id three times with the sum as the value from highest month. completely ignores other bu_ids. also is it possible to display all the bu_ids from bl_main even they are not present in bl_data. Sum would be shown as 0. Thanks a lot in advance.
Do i need to do an left inner join to get all the bu_ids from bl_main? If there are no corresponding entries in bl_data, how do i make the sum shown as zero? Thanks!
+2  A: 

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
THEn
that's SQL 2005 + much more terse than my SQL 2000 solution.
John Nolan
Oracle! Gonna try this now! Thansk
Again the same problem. it shows up all the months for each bu_id. I just want unique bu_id with the total sum and the value from the max month.Also i want all the bu_id displayed like even thought its not present in bl_data. in that case the sum should be zero.
still the same result!
A: 

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!