Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Count repeated records and add total

    I have data that I would like to count then add the amounts in one of the columns: For Example
    Item...ID...Times
    Flour...2....7
    Flour...2....3
    Flour...2....5
    Flour...4....2
    Flour...4....4
    Oil......1....3
    Oil......1....2
    Oil......1....6

    So if it could do something like this:
    Item...ID...Times
    Flour...2....7
    Flour...2....3
    Flour...2....5
    COUNT:3...TOTAL:15
    Flour...4....2
    Flour...4....4
    COUNT:2...TOTAL:6
    Oil......1....3
    Oil......1....2
    Oil......1....6
    COUNT:3...TOTAL:11


    Does this make any sense?
    It ofcourse doesn't have to look like this.

    I have made a join of the two columns like this if it makes it any easier:
    SELECT item||'-'||to_char (id) AS combined, times
    FROM tbl
    ORDER BY combined

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Output similar to the one you need could be achieved using such a query:
    Code:
    SQL> break on combined skip 2 dup
    SQL> column dummy1 noprint
    SQL> column dummy2 noprint
    SQL> column combined format a20
    SQL> compute sum of tim1 on combined
    SQL> compute count of tim2 on combined
    SQL> set pagesize 100
    SQL>
    SQL> SELECT name ||' - '|| id combined, times tim1, times tim2,
      2         'x' dummy1, 'y' dummy2
      3  FROM ITEMS
      4  ORDER BY 1;
    
    COMBINED                   TIM1       TIM2
    -------------------- ---------- ----------
    flour - 2                     7          7
    flour - 2                     3          3
    flour - 2                     5          5
    ******************** ---------- ----------
    count                                    3
    sum                          15
    
    
    flour - 4                     2          2
    flour - 4                     4          4
    ******************** ---------- ----------
    count                                    2
    sum                           6
    
    
    oil - 1                       3          3
    oil - 1                       2          2
    oil - 1                       6          6
    ******************** ---------- ----------
    count                                    3
    sum                          11
    
    
    
    8 rows selected.
    
    SQL>

  3. #3
    Join Date
    Jan 2006
    Posts
    3
    Hi littlefoot...
    I have been trying to tweak your code to work but it has not been successful.

    break on combined skip 2 dup
    column dummy1 noprint
    column dummy2 noprint
    column combined format a20
    compute sum of volume1 on combined
    compute count of volume2 on combined
    set pagesize 100

    select barcode||'-'||to_char item_row Combined, volume volume1, volume volume2 , 'x' dummy1, 'y' dummy2 , projectcode
    from item, process, copy
    where copy.wo_id=item.wo_id
    AND item.wo_id=process.wo_id
    order by 1;


    i end up getting somethign that looks like this:
    COMBINED VOLUME1 VOLUME2 DUMMY1 DUMMY2
    ---------- -------- ---------- -------- -------
    FLOUR-11 15 15 x y
    FLOUR-124 15 15 x y
    OIL-12 15 15 x y
    OIL-14 15 15 x y

  4. #4
    Join Date
    Jan 2006
    Posts
    3
    COMBINED VOLUME1 VOLUME2 DUMMY1 DUMMY2
    ---------- -------- ---------- -------- -------
    FLOUR-11....15..........15............x..............y
    FLOUR-12 ...15..........15............x..............y
    OIL-12........15..........15............x............. .y
    OIL-14........15..........15............x............. .y

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that tweaking didn't turn out well.
    If you said "column dummy1 noprint" (the same for dummy2), how come you have "x" and "y" in the output?
    Where did "projectcode" value go? Is it NULL everywhere?
    Did you get such an output from SQL*Plus or ...?

    Try the query I provided. Values it produced are not imaginary, they are result of query ran on actual data. You *should* get the same output.

    It was run on Oracle 10g, but - as far as I can tell - none of this philosophy changed since Oracle 7, so I guess the matter is not in possible different Oracle versions we use.

    Tweak the query a little bit more, until you get the correct answer. And, use CODE tags - you'll get much nicer output in the post.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Perhaps something like this..
    Code:
    SQL> select * from data;
    
    ITEM                         ID   TIMES
    -------------------- ---------- -------
    Flour                         2       7
    Flour                         2       3
    Flour                         2       5
    Flour                         4       2
    Flour                         4       4
    Oil                           1       3
    Oil                           1       2
    Oil                           1       6
    
    8 rows selected.
    
    SQL> col id for a20
    SQL> col times for a20
    SQL>
    SQL> select case when nvl(lead( i ) over( order by i ),1) <> i
      2              then cnt
      3              else i
      4          end id,
      5         case when nvl(lead( i ) over( order by i ),1) <> i
      6              then sm
      7              else times
      8          end times
      9    from (
     10  select 1 as ord,
     11         item||'-'||id i,
     12         cast( times as varchar2(100) ) times,
     13         cast( null as varchar2(1)) cnt,
     14         cast( null as varchar2(1)) as sm
     15    from data
     16  union all
     17  select 2 as ord,
     18         item||'-'||id i,
     19         null as times,
     20         'count: ' || count( * ) cnt,
     21         'total: ' || sum( times ) sm
     22    from data
     23   group by 2, item||'-'||id, null
     24         )
     25   order by i, ord
     26  /
    
    ID                   TIMES
    -------------------- --------------------
    Flour-2              7
    Flour-2              3
    Flour-2              5
    count: 3             total: 15
    Flour-4              2
    Flour-4              4
    count: 2             total: 6
    Oil-1                3
    Oil-1                2
    Oil-1                6
    count: 3             total: 11
    
    11 rows selected.
    
    SQL>
    ..should work.

Posting Permissions

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