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 Go to next message
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');
Desired result is :
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
Query Tried so far is:

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;
Kindly suggest.

Best Regards,
Re: Suggestion on count via sql statement [message #687452 is a reply to message #687451] Fri, 10 March 2023 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you "group by Name and Test Date" (actually by Name and Test Date and Instrument) how can you have several times the same name and test date for the same "Instrument" for 01-MAY-2022?

Re: Suggestion on count via sql statement [message #687453 is a reply to message #687452] Fri, 10 March 2023 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not clear how you count.
Can you post the reason of the count for each line of your result.

Re: Suggestion on count via sql statement [message #687454 is a reply to message #687452] Fri, 10 March 2023 12:49 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
This is a test case, and i have copied paste the same dates multiple time, and to emphasis also that data itself is not in great shape in original DB, there will be many activities on the same day, may it be Down, Not available or others.
Re: Suggestion on count via sql statement [message #687455 is a reply to message #687453] Fri, 10 March 2023 12:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mathguy
Messages: 108
Registered: January 2023
Senior 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
A few notes:

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
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 #687458 is a reply to message #687457] Fri, 10 March 2023 13:48 Go to previous messageGo to next message
mathguy
Messages: 108
Registered: January 2023
Senior Member
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).
Re: Suggestion on count via sql statement [message #687459 is a reply to message #687458] Fri, 10 March 2023 13:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
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 #687461 is a reply to message #687456] Fri, 10 March 2023 23:34 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear Mathguy,

Thank you for your time and assistance, It is working as expected. 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.

Will see how i can change into that and run it.

Best Regards,
Re: Suggestion on count via sql statement [message #687462 is a reply to message #687459] Fri, 10 March 2023 23:35 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear Barbara,

Thank you for your time and assistance, it is working as expected. Will try to see how it performs on a larger data set.

Best Regards,
Re: Suggestion on count via sql statement [message #687464 is a reply to message #687461] Sat, 11 March 2023 01:19 Go to previous messageGo to next message
mathguy
Messages: 108
Registered: January 2023
Senior 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
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>
Obviously we would have to change number of count columns if we increase number of instruments of interest which isn't needed if using partitioned outer join. As I said, good insentive to start learning ansi joins especially when Oracle introduced this ages ago.

SY.
Re: Suggestion on count via sql statement [message #687467 is a reply to message #687466] Sat, 11 March 2023 11:08 Go to previous messageGo to next message
mathguy
Messages: 108
Registered: January 2023
Senior Member
  order by name,
           test_date,
           instrument,
           seq -- if needed
The whole point of adding SEQ was so that we don't order by INSTRUMENT in the final query. If an ordering of rows within each (NAME, TEST_DATE) group is needed, the ordering will likely not be "in alphabetical order by the string describing the instrument".

What I am saying is that INSTRUMENT shouldn't appear in the ORDER BY clause; SEQ is there to provide the required ordering, which may or may not agree with ordering by INSTRUMENT.
Re: Suggestion on count via sql statement [message #687475 is a reply to message #687466] Mon, 13 March 2023 23:07 Go to previous message
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
Just given the context as an example of data above. Above is not an exact replica of data.
Previous Topic: Ternary Operator in PL/SQL
Next Topic: Rescheduling installments by distributing amounts
Goto Forum:
  


Current Time: Thu May 23 20:35:18 CDT 2024