Complicated problem with multi-table sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated problem with multi-table sum

Author

Topic

tbits
Starting Member

5 Posts

Posted - 2009-10-01 : 22:54:40

Hey guys, new to SQL and to the forums. My problem is this: I have machines (table) that can belong to a category (table), and each category has it's own set of maintenance programs (table). Maintenance programs may have also been used historically (table). Now, a query or view I want to have is to display the cost in a year of maintenance on machines. Both the history and the maintenance program table have the total cost listed for each individual program.

Is it possible to just list the categories distinctly with the all the maintenance program costs (both from the history and maintenance program tables) listed beside the category? Or better yet list all the different types of machines with total costs next to it (some would share the same amount of total costs as they belong to the same category and thus have the same maintenance programs). Quite the brain buster I know lol.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-01 : 23:40:04

without the table structure and relationship between the tables, i can only guess it can be something like this ..


select c.category_name, m.machine_name, sum(h.cost) as total_cost
from   machines m
       inner join category c      on m.category_id    = c.category_id
       inner join maintenance t   on c.maintenace_id  = t.maintenance_id
       inner join historically h  on c.maintenance_id = h.maintenance_id
group by c.category_name, m.machine_name

KH
[spoiler]Time is always against us[/spoiler]

tbits
Starting Member

5 Posts

Posted - 2009-10-02 : 02:28:58

Cool yeah that's what I was thinking. However what that sum(h.cost) do the sum for both current maintenance and past maintenance? I was thinking you might need to have something like say sum(h.cost) + sum(t.cost) if such a thing works.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-02 : 03:07:39

sum(h.cost) will sum the cost column in historically table.

KH
[spoiler]Time is always against us[/spoiler]

tbits
Starting Member

5 Posts

Posted - 2009-10-02 : 04:01:15

Yes which would work for the historic programs for a category however if I wanted to include current programs which were included in the maintenance table do I need to use a union statement?

E.g.

History of maintenace Table
Category: D4 Program: I001 Cost: 100.00
Category: D4 Program: I002 Cost: 103.00
Category: D2 Program: I012 Cost: 99.00

Current maintenance Table
Category: D4 Program: I005 Cost: 100.00
Category: D4 Program: I010a Cost: 120.00
Category: D2 Program: I013 Cost: 113.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:15:39

[code]select c.category_name, m.machine_name,
mc.currentcost,mc.historiccost,
mc.currentcost+mc.historiccost as total_cost
from machines m
inner join category c on m.category_id = c.category_id
inner join
(
select maintenace_id,
sum(case when cat='current' then cost else 0 end) as currentcost,
sum(case when cat='historical' then cost else 0 end) as historiccost,
from
(select maintenace_id,cost,'current' as cat
from maintenance
union all
select maintenace_id,cost,'historical'
from historically
)h
group by maintenace_id
)mc
on mc.maintenace_id=c.maintenace_id
[/code]

tbits
Starting Member

5 Posts

Posted - 2009-10-06 : 01:21:13

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources