Trying to get two counts from same joined tables

bumble · #1 (**permalink**) 04-08-09, 07:34

I have the following query:
select y.record_id, w.sample_point, w.analysis_id, w.component,y.target_samples, y.number_sampled, count( w.assigned_event) as collected
from wtm_ytf_view y,
wmm_comp_anal_samp w
where y.analysis_id = w.analysis_id
and y.analysis_version = w.analysis_version
and y.scheduled_year = w.for_year
and y.schedule = w.schedule_id
and y.record_id = w.record_id
and y.component = w.component
and y.scheduled_year = '2009'
and w.anal_status ='C'
group by y.record_id,w.sample_point,w.analysis_id,w.compone nt,y.target_samples, y.number_sampled
UNION
select y.record_id, w.sample_point, w.analysis_id, w.component,y.target_samples, y.number_sampled, count( w.date_assigned) as programmed
from wtm_ytf_view y,
wmm_comp_anal_samp w
where y.analysis_id = w.analysis_id
and y.analysis_version = w.analysis_version
and y.scheduled_year = w.for_year
and y.schedule = w.schedule_id
and y.record_id = w.record_id
and y.component = w.component
and y.scheduled_year = '2009'
and w.samp_status <>'X'
and w.anal_status <>'X'
group by y.record_id,w.sample_point,w.analysis_id,w.compone nt,y.target_samples, y.number_sampled;
But the second part of the union never populates the 'programmed' value as the selects are from the same tables...any ideas how I can get the 'programmed' value populated?
I'm after :
record_id,point,analysis,component,target,sampled, collected,programmed

r937 · #2 (**permalink**) 04-08-09, 09:29

Code:

SELECT 'collected' AS total_type
     , y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
     , COUNT( w.assigned_event ) AS total 
  FROM wtm_ytf_view y
INNER
  JOIN wmm_comp_anal_samp w
    ON y.analysis_id = w.analysis_id
   AND y.analysis_version = w.analysis_version
   AND y.scheduled_year = w.for_year
   AND y.schedule = w.schedule_id
   AND y.record_id = w.record_id
   AND y.component = w.component
 WHERE y.scheduled_year = '2009'
   AND w.anal_status ='C'
GROUP 
    BY y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
UNION ALL
SELECT 'programmed' 
     , y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
     , COUNT( w.date_assigned ) 
  FROM wtm_ytf_view y
INNER
  JOIN wmm_comp_anal_samp w
    ON y.analysis_id = w.analysis_id
   AND y.analysis_version = w.analysis_version
   AND y.scheduled_year = w.for_year
   AND y.schedule = w.schedule_id
   AND y.record_id = w.record_id
   AND y.component = w.component
 WHERE y.scheduled_year = '2009'
   AND w.samp_status <>'X'
   AND w.anal_status <>'X'
GROUP 
    BY y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled

bumble · #3 (**permalink**) 04-08-09, 10:01

Thanks! That gives me something close to what I want...I'm getting both bits of the query run but I end up with 2 sections a 'collected' section and a 'programmed' section for each row:

TOTAL_TYPE RECORD_ID SAMPLE_POI ANALYSIS_I COMPONENT TARGET_SAMPLES NUMBER_SAMPLED TOTAL
---------- ---------- ---------- ---------- ---------------------------------------- -------------- -------------- ----------
collected BLEND_01 BLEND1 NIWS comp2 240 68 2
programmed BLEND_01 BLEND1 NIWS comp2 240 68 240

what I really want is one row which has both of the counts on it...maybe it's not possible...

RECORD_ID SAMPLE_POI ANALYSIS_I COMPONENT TARGET SAMPLED PROGRAMMED COLLECTED
---------- ---------- ---------- ---------------------------------------- -------- --------- -------------- -----------
BLEND_01 BLEND1 NIWS comp2 240 68 240 2

I guess that I should say that I'm running in SQL+ (so maybe I didn't post in the right forum)....but the modified code does run

r937 · #4 (**permalink**) 04-08-09, 10:08

Code:

SELECT record_id
     , sample_point
     , analysis_id
     , component
     , target_samples
     , number_sampled
     , SUM(collected) AS total_collected 
     , SUM(programmed) AS total_programmed
  FROM (
SELECT y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
     , COUNT( w.assigned_event ) AS collected 
     , CAST(NULL AS INTEGER) AS programmed
  FROM wtm_ytf_view y
INNER
  JOIN wmm_comp_anal_samp w
    ON y.analysis_id = w.analysis_id
   AND y.analysis_version = w.analysis_version
   AND y.scheduled_year = w.for_year
   AND y.schedule = w.schedule_id
   AND y.record_id = w.record_id
   AND y.component = w.component
 WHERE y.scheduled_year = '2009'
   AND w.anal_status ='C'
GROUP 
    BY y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
UNION ALL
SELECT y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
     , CAST(NULL AS INTEGER) 
     , COUNT( w.date_assigned ) 
  FROM wtm_ytf_view y
INNER
  JOIN wmm_comp_anal_samp w
    ON y.analysis_id = w.analysis_id
   AND y.analysis_version = w.analysis_version
   AND y.scheduled_year = w.for_year
   AND y.schedule = w.schedule_id
   AND y.record_id = w.record_id
   AND y.component = w.component
 WHERE y.scheduled_year = '2009'
   AND w.samp_status <>'X'
   AND w.anal_status <>'X'
GROUP 
    BY y.record_id
     , w.sample_point
     , w.analysis_id
     , w.component
     , y.target_samples
     , y.number_sampled
       ) AS u
GROUP
    BY record_id
     , sample_point
     , analysis_id
     , component
     , target_samples
     , number_sampled

bumble · #5 (**permalink**) 04-08-09, 10:14

Fantastic!

Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech