Home » SQL & PL/SQL » SQL & PL/SQL » Suggestion on count via sql statement (Oracle 12c)
| Suggestion on count via sql statement [message #687451] |
Fri, 10 March 2023 12:25  |
 |
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
Dear Oracle Gurus,
Good morning/Good evening,
I am trying to achieve counting via SQL statement the total of instrument where it is "Down" and "Not Available", group by Name and Test Date.If Down is not availabe for that particular Name and Date, count should be 0 but Not Available count should come and vice versa.
Create table test
(Name Varchar2(20),
TEST_Date DATE,
INSTRUMENT Varchar2(200)
);
Insert into test values ( 'M-1',TO_DATE('2022/05/01 00:15:44', 'yyyy/mm/dd hh24:mi:ss'),'Down');
Insert into test values ( 'M-1',TO_DATE('2022/05/01 00:15:44', 'yyyy/mm/dd hh24:mi:ss'),'Not available');
Insert into test values ( 'M-1',TO_DATE('2022/05/01 05:10:40', 'yyyy/mm/dd hh24:mi:ss'),'COMPLICATED');
Insert into test values ( 'M-1',TO_DATE('2022/05/01 15:00:00', 'yyyy/mm/dd hh24:mi:ss'),'Down');
Insert into test values ( 'M-2',TO_DATE('2022/05/01 00:15:44', 'yyyy/mm/dd hh24:mi:ss'),'Not available');
Insert into test values ( 'M-2',TO_DATE('2022/05/01 01:25:44', 'yyyy/mm/dd hh24:mi:ss'),'Not available');
Insert into test values ( 'M-2',TO_DATE('2022/05/01 02:10:40', 'yyyy/mm/dd hh24:mi:ss'),'EASY');
Insert into test values ( 'M-2',TO_DATE('2022/05/01 04:25:44', 'yyyy/mm/dd hh24:mi:ss'),'Not available');
Insert into test values ( 'M-3',TO_DATE('2022/05/02 00:15:44', 'yyyy/mm/dd hh24:mi:ss'),'Down');
Insert into test values ( 'M-3',TO_DATE('2022/05/02 01:25:44', 'yyyy/mm/dd hh24:mi:ss'),'Down');
Insert into test values ( 'M-3',TO_DATE('2022/05/02 05:10:40', 'yyyy/mm/dd hh24:mi:ss'),'EASY');
Name Test_date Instrument count
M-1 01-MAY-2022 Down 2
M-1 01-MAY-2022 Not Available 1
M-2 01-MAY-2022 Down 0
M-2 01-MAY-2022 Not Available 3
M-3 02-MAY-2022 Down 2
M-3 02-MAY-2022 Not Available 0
SELECT Name, TRUNC(TEST_Date) AS Test_date, Instrument,
SUM(CASE WHEN Instrument = 'Down' THEN 1 ELSE 0 END) AS Down_count,
SUM(CASE WHEN Instrument = 'Not available' THEN 1 ELSE 0 END) AS Not_available_count
FROM test
WHERE Instrument IN ('Down', 'Not available')
GROUP BY Name, TRUNC(TEST_Date), Instrument
ORDER BY Name, TRUNC(TEST_Date), Instrument;
Best Regards,
|
|
|
|
|
|
|
|
|
|
| Re: Suggestion on count via sql statement [message #687455 is a reply to message #687453] |
Fri, 10 March 2023 12:51  |
|
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
Basically, i am concerned about two things "Down" or "Not Available". Suppose an instrument is down twice a day, i will report it as 2 but if it is not available that day no entry will be there with that code, in that case it will be reported 0. And my result is not correct, i was trying out, but not reaching where i want to be.
[Updated on: Fri, 10 March 2023 12:53] Report message to a moderator |
|
|
|
| Re: Suggestion on count via sql statement [message #687456 is a reply to message #687453] |
Fri, 10 March 2023 13:10 |
 |
mathguy
Messages: 77
Registered: January 2023
|
Member |
|
|
alter session set nls_date_format='dd-MON-yyyy';
with
i (instrument, seq) as (
select 'Down' , 1 from dual union all
select 'Not available', 2 from dual
)
, prep (name, test_date, instrument, count_) as (
select name, trunc(test_date), instrument, count(*)
from test
where instrument in (select instrument from i)
group by name, trunc(test_date), instrument
)
select name, test_date, instrument, nvl(count_, 0) as count_
from i left outer join prep partition by (name, test_date) using(instrument)
order by name, test_date, seq -- if needed
;
NAME TEST_DATE INSTRUMENT COUNT_
---- ----------- ------------- ----------
M-1 01-MAY-2022 Down 2
M-1 01-MAY-2022 Not available 1
M-2 01-MAY-2022 Down 0
M-2 01-MAY-2022 Not available 3
M-3 02-MAY-2022 Down 2
M-3 02-MAY-2022 Not available 0
The query should return TRUNC(TEST_DATE) in date data type; formatting shouldn't be part of it. Instead of calling TO_CHAR, I changed my NLS_DATE_FORMAT parameter first, to get dates in your required format.
COUNT is an Oracle reserved keyword, it should not be used as a column name. I added an underscore: COUNT_
The aggregate query (which I called PREP in the query) is obvious; you wanted "densification of data" (adding the rows with count of zero, which wouldn't be included in the aggregate query output). This is best done with a partitioned outer join, as I demonstrated above.
I collected together the "targeted" instruments (Down and Not available) at the very top, and added SEQ to use in ordering at the end of the query. This gives you flexibility; instead of hard-coding the values in the aggregate query, I just ask that the INSTRUMENT in that query be present in the "targeted" list.
|
|
|
|
| Re: Suggestion on count via sql statement [message #687457 is a reply to message #687456] |
Fri, 10 March 2023 13:25 |
 |
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Another method:
SCOTT@orcl_12.1.0.2.0> SELECT Name, TRUNC(TEST_Date) AS Test_date, Instrument, COUNT(*) AS count
2 FROM test
3 WHERE Instrument IN ('Down', 'Not available')
4 GROUP BY Name, TRUNC(TEST_Date), Instrument
5 UNION
6 SELECT t1.Name, TRUNC(t1.TEST_Date) AS Test_date, 'Not available' AS instrument, 0 AS count
7 FROM test t1
8 WHERE t1.Instrument = 'Down'
9 AND NOT EXISTS
10 (SELECT *
11 FROM test t2
12 WHERE t2.name = t1.name
13 AND trunc(t2.test_date) = trunc(t1.test_date)
14 AND t2.instrument = 'Not available')
15 UNION
16 SELECT t1.Name, TRUNC(t1.TEST_Date) AS Test_date, 'Down' AS instrument, 0 AS count
17 FROM test t1
18 WHERE t1.Instrument = 'Not available'
19 AND NOT EXISTS
20 (SELECT *
21 FROM test t2
22 WHERE t2.name = t1.name
23 AND trunc(t2.test_date) = trunc(t1.test_date)
24 AND t2.instrument = 'Down')
25 ORDER BY 1, 2, 3
26 /
NAME TEST_DATE INSTRUMENT COUNT
-------------------- --------------- --------------- ----------
M-1 Sun 01-May-2022 Down 2
M-1 Sun 01-May-2022 Not available 1
M-2 Sun 01-May-2022 Down 0
M-2 Sun 01-May-2022 Not available 3
M-3 Mon 02-May-2022 Down 2
M-3 Mon 02-May-2022 Not available 0
6 rows selected.
|
|
|
|
|
|
| Re: Suggestion on count via sql statement [message #687459 is a reply to message #687458] |
Fri, 10 March 2023 13:55 |
|
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Quote:
That approach doesn't look too efficient.
I assume UNION (instead of UNION ALL) is there to clean up duplicates from the second and third components. It would be better to use SELECT DISTINCT in those components, and UNION ALL as the set operator, since the components are mutually disjoint.
Moreover, it would be simpler to do two aggregations - one for Down and the other for Not available - and then do a full outer join of the results. This would go over the base data twice, rather than five times (note that the NOT EXISTS conditions add table scans).
I agree. I started out trying to do a join using the old Oracle join syntax, but gave up trying to get rid of duplicates. Then when I tried the method that I finally posted, I had a similar problem. I finally realized that I had "Available" beginning with a capital "A" and "available" beginning with a lower case "a". Once I fixed that it worked OK. In hindsight that was probably the problem I had with join to the select from dual.
[Updated on: Fri, 10 March 2023 13:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Suggestion on count via sql statement [message #687464 is a reply to message #687461] |
Sat, 11 March 2023 01:19 |
|
mathguy
Messages: 77
Registered: January 2023
|
Member |
|
|
akssre wrote on Fri, 10 March 2023 23:34
I am still trying to understand the sql, as i am more familiar with Select * from test a, test b where a.name = b.name approach. just trying to convert into that.
I don't think you can. You don't need to use the USING clause as I did, you can write ON I.INSTRUMENT = PREP.INSTRUMENT. If you insist on the "old" syntax, you can write WHERE I.INSTRUMENT = PREP.INSTRUMENT(+) (note the plus operator in parentheses, since we want an outer join) - and use the comma operator in the FROM clause to separate the tables (view and table); but what you cannot do with that syntax is the PARTITION BY clause, which is critical for data densification.
In any case, the syntax you are more familiar with (called "Oracle join syntax" or "comma-separated join syntax") is not recommended. Even Oracle in its own documentation encourages programmers to use the ANSI join syntax, where the keyword JOIN appears explicitly in the FROM clause, and the join condition is in an ON clause - not in the WHERE clause - or, as I did in my code, in a simpler USING clause. For example: Joins
Quote:
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator.
There are many things you simply can't do with the "old" syntax. For example, the PARTITION BY clause for a partitioned outer join. If you wanted to adopt the other approach I suggested (select for 'Down' and for 'Not available' separately, and then do a full outer join), that too can only be done with the ANSI join syntax - there is no "old" syntax for full outer joins.
|
|
|
|
| Re: Suggestion on count via sql statement [message #687466 is a reply to message #687464] |
Sat, 11 March 2023 09:50 |
Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
If OP isn't comfortable with ANSI joins (although mathguy provided solution should be a good insentive to start) we can avoid outer join and use something like (it could be done using unpivot but I have a feeling OP isn't comfortable with pivot/unpivot either):
with i(instrument,seq)
as (
select 'Down' , 1 from dual union all
select 'Not available', 2 from dual
),
prep(name,test_date,count1,count2)
as (
select t.name,
trunc(t.test_date),
count(case i.seq when 1 then 1 end) count1,
count(case i.seq when 2 then 1 end) count2
from test t,
i
where t.instrument = i.instrument
group by t.name,
trunc(t.test_date)
)
select p.name,
p.test_date,
i.instrument,
case i.seq
when 1 then p.count1
else p.count2
end count_
from prep p,
i
order by name,
test_date,
instrument,
seq -- if needed
/
NAME TEST_DATE INSTRUMENT COUNT_
-------------------- --------- ------------- ----------
M-1 01-MAY-22 Down 2
M-1 01-MAY-22 Not available 1
M-2 01-MAY-22 Down 0
M-2 01-MAY-22 Not available 3
M-3 02-MAY-22 Down 2
M-3 02-MAY-22 Not available 0
6 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: Suggestion on count via sql statement [message #687475 is a reply to message #687466] |
Mon, 13 March 2023 23:07 |
|
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
There is something wrong in the code, it is not behaving exactly it should, and giving for some wells as below :-
NAME TEST_DATE INSTRUMENT COUNT_
-------------------- --------- ------------- ----------
M-1 01-MAY-22 Down 0
M-1 01-MAY-22 Not available 1
M-1 01-MAY-22 Down 0
M-1 01-MAY-22 Down 9
M-1 01-MAY-22 Not available 1
M-1 01-MAY-22 Not available 0
|
|
|
|
Goto Forum:
Current Time: Mon Sep 25 22:04:50 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
