No question at this time
DBA Top 10
1 M. Cadot 16900
2 A. Kavsek 15000
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4700
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48095
Total active users1706
Act. users last 24h8
Act. users last hour0
Registered user hits last week389
Registered user hits last month1490
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, 8 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, 8 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, 8 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