Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Count (*) in Oracle SQL queries!

    I have a problem with count(*) function using GROUP BY while counting the total of records is not equal the sum of subgroups. Please see the attached text file for detail queries. I expect the results of total_pieces equals the sum of bundle_pieces. Any input is much appreciated. Thanks in advance.
    Attached Files Attached Files
    Last edited by avt2k6; 04-03-08 at 12:12.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Re-posting the queries from attachment:

    Query A:
    Code:
    SELECT a.ct_groupid_ans,a.ct_type_ans,a.ct_sort_level_ans,
      a.rate_code_ans,a.ct_dest_zip_code, a.carrier_rte_code, b.overflow_ind,
      a.piece_thickness_amt, a.piece_weight_amt, count(*) as TOTAL_PIECES
    FROM record_vw a LEFT OUTER JOIN container_data_t b 
    on a.ct_groupid_ans = b.ct_groupid_ans 
    WHERE a.electronic_file_id = 'A12' AND b.electronic_file_id = 'A12'
      and a.ct_groupid_ans = '2'
    GROUP BY a.ct_groupid_ans,a.ct_type_ans,a.ct_sort_level_ans,
      a.rate_code_ans, a.ct_dest_zip_code,a.carrier_rte_code,b.overflow_ind,
      a.piece_thickness_amt, a.piece_weight_amt
    ORDER BY a.ct_groupid_ans
    Query B:
    Code:
    SELECT bundle_ind,bundle_id_ans as bundle_id_ans,bundle_sort_level_ans,
      rate_code_ans, bundle_dest_ans,carrier_rte_code, count(*) as BUNDLE_PIECES
    FROM record_vw
    WHERE electronic_file_id = 'A12' and ct_groupid_ans = '2'
    GROUP BY bundle_ind,bundle_id_ans,bundle_sort_level_ans,rate_code_ans,
     bundle_dest_ans,carrier_rte_code
    ORDER BY ct_groupid_ans
    > I expect the results of total_pieces equals the sum of bundle_pieces.
    I do not expect so, as more rows in B may be joined to one row in A. The result will contain more rows.
    Just the demonstration:
    Code:
    SQL> -- select from 5-row table
    SQL> with a as (select level val from dual connect by level <= 5)
      2  select * from a;
    
           VAL
    ----------
             1
             2
             3
             4
             5
    
    5 rows selected.
    
    SQL> -- when left joined with multiple rows in table B
    SQL> with a as (select level val from dual connect by level <= 5),
      2       b as (select 1 val from dual connect by level <= 3)
      3  select * from a left join b on a.val = b.val;
    
           VAL        VAL
    ---------- ----------
             1          1
             1          1
             1          1
             5
             4
             3
             2
    
    7 rows selected.
    
    SQL>
    By the way, is CT_GROUPID_ANS primary key of any of the tables/views?

  3. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Ans: CT_GROUPID_ANS is not primary key of any of the tables/views.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Just to explain the question: in query A, you join the tables on CT_GROUPID_ANS. If there are duplicates, your output contains all combinations of that duplicates.

    Example:
    Table A: 12 rows with CT_GROUPID_ANS=1
    Table B: 4 rows with CT_GROUPID_ANS=1
    Resultset: 12*4=48 rows for the above source rows.

    I do not know your data model (table structures and their relationship). But if you do not want this behavior, enhance the JOIN with condition(s) on another columns (if there are any). Ideally, one set of that columns shall be the primary or unique key.

Posting Permissions

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