Count repeated records and add total

jelost · #1 (**permalink**) 01-19-06, 16:49

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

Littlefoot · #2 (**permalink**) 01-19-06, 17:17

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>

jelost · #3 (**permalink**) 01-20-06, 15:02

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

jelost · #4 (**permalink**) 01-20-06, 15:04

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

Littlefoot · #5 (**permalink**) 01-20-06, 15:53

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.

JMartinez · #6 (**permalink**) 01-20-06, 16:40

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech