Home » SQL & PL/SQL » SQL & PL/SQL » Selective count with a condition (Oracle SQL 11G)
Selective count with a condition [message #655262] |
Thu, 25 August 2016 19:41 |
|
vickytalware
Messages: 4 Registered: August 2016
|
Junior Member |
|
|
Hello,
I wanted to know the best way to solve the below scenario, where I've an ID, date, type & state in one table.
Accepted is the end state, once the id reaches accepted state no further updates will occur for that ID.
For example, this is how the data in database table looks like
Date |ID | Type | State
-------+-----+----------+---------------
08/06 |12 | 3 | Rejected --Should not be considered in the non accepted bucket as this id is already accepted.
08/06 |12 | 2 | Timeout --Should not be considered in the non accepted bucket as this id is already accepted.
08/06 |12 | 1 | Accepted --Counted in Accepted bucket.
08/07 |45 | 2 | Rejected --Counted in Non Accepted bucket as they have not been accepted yet.
08/08 |67 | 3 | Timeout --Counted in Non Accepted bucket
Now, I want to count ids into Accepted and Not Accepted buckets which would look like this
Expected results
Date | Accepted | Not Accepted
-------+----------+--------------
08/06 | 1 | 0
08/07 | 0 | 1
08/08 | 0 | 1
What I'm getting using
SELECT ID,
SUM(
CASE
WHEN TYPE = 1
THEN 1
ELSE 0
END) AS accepted,
SUM(
CASE
WHEN TYPE != 1
THEN 1
ELSE 0
END) AS nonaccepted
FROM MY_TABLE
GROUP BY ID;
ID | Accepted | Not Accepted
-------+----------+--------------
12 | 1 | 2
45 | 0 | 1
67 | 0 | 1
[Updated on: Fri, 26 August 2016 00:34] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Selective count with a condition [message #655278 is a reply to message #655262] |
Fri, 26 August 2016 01:02 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You should not be using date as a column name, as it is an Oracle reserved word. The following includes an example of the type of create table and insert statements that we expect from you, as well as a couple of potential solutions. I selected and grouped by your id column, but you could select and group by your date column with another column name instead. I used decode, but you could use case if you prefer.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE my_table
2 (id NUMBER,
3 type NUMBER)
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO my_table (id, type) VALUES (12, 3)
3 INTO my_table (id, type) VALUES (12, 2)
4 INTO my_table (id, type) VALUES (12, 1)
5 INTO my_table (id, type) VALUES (45, 2)
6 INTO my_table (id, type) VALUES (67, 3)
7 SELECT * FROM DUAL
8 /
5 rows created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM my_table
2 /
ID TYPE
---------- ----------
12 3
12 2
12 1
45 2
67 3
5 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT id, accepted,
2 DECODE (accepted, 0, not_accepted, 0) not_accepted
3 FROM (SELECT id,
4 SUM (DECODE (type, 1, 1, 0)) accepted,
5 SUM (DECODE (type, 1, 0, 1)) not_accepted
6 FROM my_table
7 GROUP BY id)
8 ORDER BY id
9 /
ID ACCEPTED NOT_ACCEPTED
---------- ---------- ------------
12 1 0
45 0 1
67 0 1
3 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT id,
2 SUM (DECODE (type, 1, 1, 0)) accepted,
3 DECODE (SUM (DECODE (type, 1, 1, 0)),
4 0, SUM (DECODE (type, 1, 0, 1)),
5 0) not_accepted
6 FROM my_table
7 GROUP BY id
8 ORDER BY id
9 /
ID ACCEPTED NOT_ACCEPTED
---------- ---------- ------------
12 1 0
45 0 1
67 0 1
3 rows selected.
|
|
|
|
Re: Selective count with a condition [message #655315 is a reply to message #655302] |
Fri, 26 August 2016 23:25 |
|
vickytalware
Messages: 4 Registered: August 2016
|
Junior Member |
|
|
SCOTT@orcl_12.1.0.2.0> SELECT id,
2 SUM ( DISTINCT DECODE (type, 1, 1, 0)) accepted,
3 DECODE (SUM (DISTINCT DECODE (type, 1, 1, 0)),
4 0, SUM (DISTINCT DECODE (type, 1, 0, 1)),
5 0) not_accepted
6 FROM my_table
7 GROUP BY id
8 ORDER BY id
9 /
ID ACCEPTED NOT_ACCEPTED
---------- ---------- ------------
12 1 0
45 0 1
67 0 1
I slightly modified the above solution (added DISTINCT) suggested by @@Barbara Boehmer.
Thank you, @Barbara Boehmer & @ricky_s I really appreciate your help and you guys saved my day.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:26:56 CDT 2024
|