No question at this time
DBA Top 10
1 B. Vroman 14400
2 M. Cadot 14200
3 A. Kavsek 7100
4 T. Boles 6100
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3600
7 G. Lambregts 1100
7 T. P 1100
7 P. Wisse 1100
10 B. Derous 500
10 R. Wauben 500
10 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48226
Total active users1641
Act. users last 24h5
Act. users last hour0
Registered user hits last week355
Registered user hits last month1464
Go up

Total Count with 2 tables
Next thread: TEMP Tablespace queries have given different values
Prev thread: RAC(ASM) with single instance(standby)

Message Score Author Date
Hi, Anyone can help on sql statement on how to ...... Ferdie Palero Dec 06, 2017, 12:58
FULL OUTER JOIN. ... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Dec 06, 2017, 14:52
Hello Ferdie, (this seems related to http://www...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Dec 06, 2017, 18:44

Follow up by mail Click here


Subject: Total Count with 2 tables
Author: Ferdie Palero, Philippines
Date: Dec 06, 2017, 12:58, 227 days ago
Os info: Centos Linux
Oracle info: Oracle11g
Message: Hi,

Anyone can help on sql statement on how to get the total count of the 2 tables.

1st table data SUCCESS;
select substr(trim(regtime),1,2),count(*) Failed, null as SUCCESS from failed_history where trim(regdate)=to_char(sysdate-9,'DD Mon YYYY')
group by substr(trim(regtime),1,2) order by substr(trim(regtime),1,2);

HR SUCCESS F
-- ---------- -
01 1
07 4
08 6
09 16
10 10
11 9
12 10
13 8
14 17
15 12
16 4
17 7
19 1

2nd table FAILED;
select substr(trim(regtime),1,2),count(*) Success, null as Failed from success_history where trim(regdate)=to_char(sysdate-9,'DD Mon YYYY')
group by substr(trim(regtime),1,2) order by substr(trim(regtime),1,2);

HR FAILED S
-- ---------- -
07 5
08 14
09 15
10 8
11 34
12 12
13 10
14 25
15 11
16 9
17 12
18 6
19 3
20 1

Need Total Count every hour output
HR SUCCESS FAILED
-- ---------- ---------
01 1 0
07 4 5
08 6 14
09 16 15
10 10 8
11 9 34
12 10 12
13 8 10
14 17 25
15 12 11
16 4 9
17 7 12
18 0 6
19 1 3
20 0 1

Thanks in advanced.

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Total Count with 2 tables
Author: Michel Cadot, France
Date: Dec 06, 2017, 14:52, 227 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
FULL OUTER JOIN.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Total Count with 2 tables
Author: Bruno Vroman, Belgium
Date: Dec 06, 2017, 18:44, 227 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Ferdie,

(this seems related to http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=88885)

And what about "holes"? if you have data like
S

01 1
07 4

F
05 3
07 2
do you want a result like
HR S F

01 1 0
05 0 3
07 4 2
or would you like to fill in the gaps like
HR S F

00 0 0
01 1 0
02 0 0
03 0 0
04 0 0
05 0 3
06 0 0
07 4 2
and so on?
In the later case, generate the hours with for example
WITH hours AS ( SELECT LEVEL - 1 hr FROM dual CONNECT BY LEVEL <= 23)
and do your SELECT against this list of hours, outer-joining both "S" and "F" tables.

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here