Hi Guys,

DB2 LUW v9.5

I have two tables that does information from a Machine -- Active and
Inactive. Also, machine belongs to a department.

I want to find out the count of Active and Inactive machines that
belongs to each Department. I could do a simple:

select DEPARTMENT_ID, ACTIVE, INACTIVE

from
(
select DEP.DEPARTMENT_ID , COUNT (M.MACHINE_ID) as Active
from TBL_ACTIVE M, TBL_department DEP
where M.DEPARTMENT_ID = DEP.DEPARTMENT_ID group by
DEP.DEPARTMENT_ID
) as A,

(
select DEP.DEPARTMENT_ID , COUNT (M.MACHINE_ID) as INACTIVE
from TBL_INACTIVE M, TBL_department DEP
where M.DEPARTMENT_ID = DEP.DEPARTMENT_ID group by
DEP.DEPARTMENT_ID
) as I

where A.DEPARTMENT_ID = I.DEPARTMENT_ID

Performance of this simple query is critical and I am looking for ways
to optimize it.

I was hoping to find a better solution or one that does NOT scan any
tables more than once -- in the example above, TBL_DEPARTMENT was used
twice.

Any ideas?

Thanks

Try:

select DEP.DEPARTMENT_ID
, COUNT (A.MACHINE_ID) as Active
, COUNT (N.MACHINE_ID) as Inactive
from TBL_department DEP
left outer join
TBL_ACTIVE A
on A.DEPARTMENT_ID = DEP.DEPARTMENT_ID
left outer join
TBL_INACTIVE N
on N.DEPARTMENT_ID = DEP.DEPARTMENT_ID
group by
DEP.DEPARTMENT_ID
;

On Aug 20, 7:46*am, Michel Esber <mic... (AT) automatos (DOT) com> wrote:
Is changing the schema an option? This looks more like a database
design issue than a query issue. Status (active or inactive) is an
attribute of a MACHINE, so I'm not sure why you are separating them
into separate tables. Having a single MACHINE table with a STATUS
column may make more sense.

If it's not an option, how about something like:

with allmachines (department_id, machine_id, status) as
(select department_id, machine_id, 'A' from tbl_active
union
select department_id, machine_id, 'I' from tbl_inactive
)
select
all.department_id,
sum(case when all.status = 'A' then 1 else 0 end) as active,
sum(case when all.status = 'I' then 1 else 0 end) as inactive
from
allmachines all,
tbl_department m
where
all.department_id = m.department_id
group by
all.department_id;


Ian Bjorhovde

On Aug 21, 2:27*am, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
1)I'm sorry. The query will produce incorrect results.

Please use DISTINCT keywords, like:

select DEP.DEPARTMENT_ID
, COUNT (DISTINCT A.MACHINE_ID) as Active
, COUNT (DISTINCT N.MACHINE_ID) as Inactive
from TBL_department DEP
left outer join
TBL_ACTIVE A
on A.DEPARTMENT_ID = DEP.DEPARTMENT_ID
left outer join
TBL_INACTIVE N
on N.DEPARTMENT_ID = DEP.DEPARTMENT_ID
group by
DEP.DEPARTMENT_ID
;

2) Using UNION ALL/UNION(as Ian wrote) may give you a better
performance than using LEFT OUTER JOIN, like:

select DEP.DEPARTMENT_ID
, COUNT (CASE all.status WHEN 'A' THEN 0 END) as Active
, COUNT (CASE all.status WHEN 'I' THEN 0 END) as Inactive
from TBL_department DEP
left outer join
(select DEPARTMENT_ID , MACHINE_ID , 'A' as status
fom TBL_ACTIVE
union all
select DEPARTMENT_ID , MACHINE_ID , 'I' as status
fom TBL_INACTIVE
) all
on all.DEPARTMENT_ID = DEP.DEPARTMENT_ID
group by
DEP.DEPARTMENT_ID
;

On 20 ago, 17:00, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

Hi Tonkuma and Ian,

Thanks for your suggestions.

Please check: http://tinyurl.com/2dcagyg

I have a recursive query to create tree of Departments and Parents,
and now I need to count the number of descendent machines a department
has,
and also the number of machines a specific department has. Somehow,
this is not working anymore.

Something like:



with t_recurs (customer_id, department_id, department_name,
department_parent, iter) as

( select customer_id, department_id, department_name, department_id, 0
from asset.tbl_asset_department

union all
select r.customer_id, r.department_id, r.department_name,
d.department_parent, iter + 1
from t_recurs r, asset.tbl_asset_department d
where r.department_parent = d.department_id and iter < 1000 )


select
r.customer_id,
r.department_id,
r.department_name,
r.department_parent,
-- count(machine_id) as total_machine_below_hierarchy, ??? -- Not
workiing ???
count(m.machine_id) as total_machine_this_department,


from
t_recurs r left outer join TBL_ACTIVE m on (r.department_parent =
m.department_id)

where

r.department_id <> r.department_parent

group by r.customer_id, r.department_id, r.department_name,
r.department_parent;


PS: According to the other post, a root department has its department
parent as value -2.

Any ideas on how to get this hierarchy count to work?

Thanks,
-M

Active/inactive is a status that has a duration. Your data model is
wrong which is why you are doing UNIONs and other kludges. Read this
article on attribute splitting:

http://www.simple-talk.com/sql/t-sql...ting-headache/

Would you keep your personnel in two tables? Male_Personnel and
Female_Personnel or in one Personnel table with a gender attribute?

For your second question, use the Nested Sets model for the hierarchy
and those queries are easy.

Also, we do not use "tbl-" affixes on table names. This is SQL, not
1960's BASIC. The compiler does not ned the hint. The colletive or
plutal noun used for the name will tell the programmer what it is as
per ISO-11179 rules.

Based on a few decades of cleaning up bad designs, if you re-do this
schema properly, you ought to get a 1-2 orders of magntiude increase
in performance and it will be maintainble.