1 Reply Latest reply: Oct 4, 2012 10:51 AM by Frank Kulash RSS

    query in sql

    949210
      oracle database version 10.2.0.1 on windows server 2003
      segment_id is a common column in two tables "segment" and "mv_cat_seg_reg_prod"
      mv_cat_seg_prod can have more than a single row for each segment_id
      whereas segment table has one row for each segment_id(not more than one)
      segment_id column in the segment table is not sequential
      segid
      --------
      1
      2
      5
      8
      78
      again:for a single segment_id there may be one or more rows
      i need to count number of rows in mv_cat_seg_reg_prod for each segment_id
      there are thousands of segments so how do i count
      select count(*) from mv_cat_seg_reg_prod where segment_id=first segment_id of segmentid column of segment table
      select count(*) from mv_cat_seg_reg_prod where segment_id=second segment_id of segmentid column of segment table
      select count(*) from mv_cat_seg_reg_prod where segment_id=third segment_id of segmentid column of segment
      table
      select count(*) from mv_cat_seg_reg_prod where segment_id=fourth segment_id of segmentid column of segment table
      and so on
      please help me automate it
      thanks in advance
        • 1. Re: query in sql
          Frank Kulash
          Hi,

          Join the two tables.
          If you want to include results for all values in the segment table, even if they have 0 matching rows in mv_cat_seg_reg_prod, then make it an outer join, like this:
          SELECT       s.segment_id
          ,       COUNT (m.segment_id)     AS cnt
          ,       ROW_NUMBER () OVER (ORDER BY  s.segment_id)
                                          AS r_num     -- if wanted
          FROM           segment           s
          LEFT OUTER JOIN      mv_cat_seg_reg_prod  m  ON  m.segment_id = s.segment_id
          GROUP BY  s.segment_id
          ORDER BY  s.segment_id
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data. If any of your tables are views (materialized or otherwise) then pretend they're actaully real tables, and post CREATE TABLE and INSERT statemnts that mimic the relevnt columns in the vies.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}