OraFAQ Forum: SQL & PL/SQL » Find no. of times column value modified

Home » SQL & PL/SQL » SQL & PL/SQL » Find no. of times column value modified (TOAD - 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find no. of times column value modified [message #604097]

Tue, 24 December 2013 00:26

dhivyak
Messages: 6
Registered: December 2013

Junior Member

Need Query to find out how many times column value is modified(insert/update) for a particular ID? Null values are also allowed

E.g:
table is as follows:
ID Col1
1 Y
1 N
1 N
1 N
2 NULL
2 Y
2 N
2 N
3 Y
3 Y

The desired output should be :
ID Col1
1 2(one insert and one update)
2 3(one insert and 2 updates)
3 1(one insert)

Any help would be appreciated.

Thanks

Report message to a moderator

Re: Find no. of times column value modified [message #604099 is a reply to message #604097]

Tue, 24 December 2013 00:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Find no. of times column value modified [message #604101 is a reply to message #604097]

Tue, 24 December 2013 00:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you have nothing to show the order of the rows (a time, a sequence number...) there is no difference between:
1 Y
1 N
1 N
1 N
and
1 N
1 Y
1 N
1 N
and
1 N
1 N
1 N
1 Y
and so on.

So if you have not this order column there is no ways to do what you want.

Report message to a moderator

Re: Find no. of times column value modified [message #604102 is a reply to message #604101]

Tue, 24 December 2013 00:57

dhivyak
Messages: 6
Registered: December 2013

Junior Member

There is a order based on the date. Based on the date need a query to find out the no. of times column modified.

Report message to a moderator

Re: Find no. of times column value modified [message #604103 is a reply to message #604102]

Tue, 24 December 2013 01:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So post a test case that represents your issue.
And post the EXACT output you want for this test case.

[Updated on: Tue, 24 December 2013 01:11]

Report message to a moderator

Re: Find no. of times column value modified [message #604104 is a reply to message #604103]

Tue, 24 December 2013 01:21

dhivyak
Messages: 6
Registered: December 2013

Junior Member

table is as follows:
ID Col1 Date
1 Y 1/25/2013
1 N 1/26/2013
2 NULL 1/26/2013
1 N 1/27/2013
2 Y 1/27/2013
1 N 1/28/2013
3 NULL 1/29/2013
2 N 1/29/2013
2 N 1/30/2013
3 Y 1/31/2013

Desired output:
ID Col1
1 2(one insert and one update)
2 3(one insert and 2 updates)
3 2(one insert and one update)

Report message to a moderator

Re: Find no. of times column value modified [message #604105 is a reply to message #604104]

Tue, 24 December 2013 01:29

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You didn't read the link, did you?

Report message to a moderator

Re: Find no. of times column value modified [message #604107 is a reply to message #604105]

Tue, 24 December 2013 02:48

dhivyak
Messages: 6
Registered: December 2013

Junior Member

Am not able to view the details of the link

Report message to a moderator

Re: Find no. of times column value modified [message #604108 is a reply to message #604107]

Tue, 24 December 2013 02:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Hard to believe.
Try this one: http://www.orafaq.com/wiki/Test_case

Report message to a moderator

Re: Find no. of times column value modified [message #604111 is a reply to message #604108]

Tue, 24 December 2013 03:47

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with 
...
 22  select id, count(flg) cnt
 23  from flagged
 24  group by id
 25  /
        ID        CNT
---------- ----------
         1          2
         2          3
         3          2

3 rows selected.

Report message to a moderator

Re: Find no. of times column value modified [message #604116 is a reply to message #604111]

Tue, 24 December 2013 04:10

dhivyak
Messages: 6
Registered: December 2013

Junior Member

PFB the details

CREATE TABLE table1
(
ID NUMBER NOT NULL,
IND VARCHAR2 (1 BYTE),
C_DATE DATE
)
/
INSERT INTO table1 (id, ind, c_date)
VALUES (1, 'Y', SYSDATE)
/
INSERT INTO table1
VALUES (1, 'N', SYSDATE)
/
INSERT INTO table1
VALUES (2, NULL, SYSDATE)
/
INSERT INTO table1
VALUES (1, 'N', SYSDATE)
/
INSERT INTO table1
VALUES (2, 'Y', SYSDATE)
/
INSERT INTO table1
VALUES (1, 'N', SYSDATE)
/
INSERT INTO table1
VALUES (3, NULL, SYSDATE)
/
INSERT INTO table1
VALUES (2, 'N', SYSDATE)
/
INSERT INTO table1
VALUES (2, 'N', SYSDATE)
/
INSERT INTO table1
VALUES (3, 'Y', SYSDATE)
/
INSERT INTO table1
VALUES (3, 'Y', SYSDATE)
/

SELECT *
FROM TABLE1
ORDER BY Id
/

ID IND C_DATE
1 N 2/24/2013 3:18:12 PM
1 N 2/25/2013 4:21:30 PM
1 Y 2/26/2013 7:06:45 PM
1 N 2/27/2013 8:38:56 PM

2 NULL 2/27/2013 2:59:06 PM
2 Y 2/28/2013 1:16:13 PM
2 N 2/28/2013 8:07:34 PM
2 N 3/9/2013 3:42:30 PM

3 NULL 3/11/2013 9:19:23 PM
3 Y 3/25/2013 3:16:45 PM
3 Y 3/29/2013 3:16:45 PM

My Requirement is to write q query to find how many ID's have IND column inserted/updated

Output should be:
ID Count()
1 3
2 3
1 2

For ID 1 the record 1 is insert and record 3 and 4 are modified (but total 4 records)
For ID 2 the record 1 is insert and record 2 and 3 are modified (but total 4 records)
For ID 2 the record 1 is insert and record 2 is modified (but total 3 records)

Report message to a moderator

Re: Find no. of times column value modified [message #604117 is a reply to message #604116]

Tue, 24 December 2013 04:21

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I doubt your INSERT statements will give the result of your SELECT.

Report message to a moderator

Re: Find no. of times column value modified [message #604119 is a reply to message #604117]

Tue, 24 December 2013 04:31

dhivyak
Messages: 6
Registered: December 2013

Junior Member

This Query

select id, count(IND) cnt
from table1
group by id

will fetch the following result:
ID CNT
1 4
2 3
3 1

but the required Output is :
ID CNT
1 3
2 3
3 2

Report message to a moderator

Re: Find no. of times column value modified [message #604121 is a reply to message #604119]

Tue, 24 December 2013 04:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 24 December 2013 08:10

So post a test case that represents your issue.
And post the EXACT output you want for this test case.

Report message to a moderator

Previous Topic:	How to restrict the rows while generating XML
Next Topic:	get row counts of tables in schema without using dba_tables.num_rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 21:34:01 CDT 2024