Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Question Unanswered: Trying to get two counts from same joined tables

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    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
    Last edited by bumble; 04-08-09 at 10:08.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2009
    Posts
    3

    Thumbs up

    Fantastic! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •