selecting from a query

fullymooned · #1 (**permalink**) 08-19-04, 17:34

I generate a query which contains the following
date, workorder, downtime, mttr, deptgroup, status

I need to display the following variables in a report
Could someone help me with the syntax

mach_dt = sum(downtime) where deptgroup = 1
mach_wo = count(workorder) where deptgroup = 1 and status = 1 or 2
pna_dt = sum(downtime) where deptgroup = 2

thank you

Pat Phelan · #2 (**permalink**) 08-20-04, 00:33

When you count the workorders, if workorder X appears 5 times do you want it counted once, or all 5 times it appears? What database engine (DB2, MS-SQL, MySQL, Oracle, etc) are you using?

-PatP

r937 · #3 (**permalink**) 08-20-04, 09:42

Code:

select 'mach_dt'     as total_type
     , sum(downtime) as total_amount
  from yourquery
 where deptgroup = 1
union all
select 'mach_wo'    
     , count(workorder) 
  from yourquery
 where deptgroup = 1 
   and status in (1,2))
union all
select 'pna_dt'
     , sum(downtime) 
  from yourquery
 where deptgroup = 2

Internet Services

Web Development

Digital Marketing

Consumer Tech