Home » SQL & PL/SQL » SQL & PL/SQL » How to bypass putting select inside a count function in the following query ? (oracle 10G)
How to bypass putting select inside a count function in the following query ? [message #569136] Sun, 21 October 2012 05:32 Go to next message
daudiam
Messages: 51
Registered: June 2011
Member
I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :

1. The number of assignments created from it (after a given constant assign date)
2. The number of assignments created from it that have been approved.
3. The number of unique users who have either created or approved an assignment from it

Test data :

create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int, 
approver_id int, assign_date timestamp, approved_yn varchar2(10));  

insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');

insert into assignment values (1, 1, 100, 200, 100, to_date('23-10-2012', 'dd-mm-yyyy'), 'Y'); 
insert into assignment values (2, 2, 200, 100, 200, to_date('22-10-2012', 'dd-mm-yyyy'), 'N'); 
insert into assignment values (3, 2, 100, 200, 101, to_date('24-10-2012', 'dd-mm-yyyy'), 'Y'); 
insert into assignment values (4, 1, 102, 101, 200, to_date('21-10-2012', 'dd-mm-yyyy'), 'Y'); 
insert into assignment values (5, 1, 200, 100, 102, to_date('23-10-2012', 'dd-mm-yyyy'), 'N'); 
insert into assignment values (6, 2, 100, 200, 100, to_date('20-10-2012', 'dd-mm-yyyy'), 'Y'); 
insert into assignment values (7, 1, 200, 102, 100, to_date('19-10-2012', 'dd-mm-yyyy'), 'N'); 

Assignment :

id     r_paper_id   assigner_id    assignee_id   approver_id    assign_date                    approved_yn
-----------------------------------------------------------------------------------------------------------

1	1	     100	    200	         100	        23-10-12 12:00:00.000000000 AM	Y
2	2	     200	    100	         200	        22-10-12 12:00:00.000000000 AM	N
3	2	     100	    200	         101	        24-10-12 12:00:00.000000000 AM	Y
4	1	     102	    101	         200	        21-10-12 12:00:00.000000000 AM	Y
5	1	     200	    100	         102	        23-10-12 12:00:00.000000000 AM	N
6	2	     100	    200	         100	        20-10-12 12:00:00.000000000 AM	Y
7	1	     200	    102	         100	        19-10-12 12:00:00.000000000 AM	N

Research_paper:

id   name
----------
1	A
2	B


Expected result :

r_paper_id   created   approved   unique_users
-----------------------------------------------
1            3         2          4
2            3         2          3


I wrote the following query for that :

SELECT rp.id r_paper_id,
  COUNT(*) created,
  COUNT(
  CASE
    WHEN a.approved_yn = 'Y'
    THEN 1
    ELSE NULL
  END) approved,
  COUNT(DISTINCT
  (SELECT a1.assigner_id FROM assignment a1 WHERE a1.id = a.id
  UNION
  SELECT a2.approver_id FROM assignment a2 WHERE a2.id = a.id
  )) unique_users
FROM assignment a,
  research_paper rp
WHERE a.r_paper_id = rp.id
AND a.assign_date >= to_date('20-10-2012', 'dd-mm-yyyy')
GROUP BY rp.id ;


But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.

[Updated on: Sun, 21 October 2012 05:49] by Moderator

Report message to a moderator

Re: How to bypass putting select inside a count function in the following query ? [message #569138 is a reply to message #569136] Sun, 21 October 2012 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER use subquery in SELECT until you'll be a SQL expert (and this is the root of your error).
Rewrite your queries without using this.

Regards
Michel
Re: How to bypass putting select inside a count function in the following query ? [message #569142 is a reply to message #569138] Sun, 21 October 2012 07:01 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
I can't think of a way to get unique users from 2 different fields without using UNION, and in the union, I have to relate to the id of the original assignment, and hence the union will have to be in a subquery. But even if I could write the subquery correctly, that subquery will run for (almost) every assignment, and this will be bad, but I am not able to come up with an alternative.
Re: How to bypass putting select inside a count function in the following query ? [message #569145 is a reply to message #569142] Sun, 21 October 2012 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove all SELECT in SELECT and try to use only joins which means drop down the SELECT in SELECT in FROM clause.

Regards
Michel
Re: How to bypass putting select inside a count function in the following query ? [message #569147 is a reply to message #569145] Sun, 21 October 2012 09:10 Go to previous messageGo to next message
daudiam
Messages: 51
Registered: June 2011
Member
I removed the nested select, but I need a list of both approvers and assigners, in order to join them and find the unique ones. The following code will only pick the count of unique user ids from both the lists, but won't give the overall unique users :
SELECT rp.id r_paper_id,
  COUNT(*) created,
  COUNT(
  CASE
    WHEN a.approved_yn = 'Y'
    THEN 1
    ELSE NULL
  END) approved,
  count(distinct(a.approver_id)) + count(distinct(a.assigner_id)) unique_users
FROM assignment a,
  research_paper rp
WHERE a.r_paper_id = rp.id
AND a.assign_date >= to_date('20-10-2012', 'dd-mm-yyyy')
GROUP BY rp.id ;
Re: How to bypass putting select inside a count function in the following query ? [message #569149 is a reply to message #569147] Sun, 21 October 2012 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't try: "which means drop down the SELECT in SELECT in FROM clause".

Regards
Michel
Re: How to bypass putting select inside a count function in the following query ? [message #569150 is a reply to message #569149] Sun, 21 October 2012 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is:
SQL> SELECT a.r_paper_id,
  2    COUNT(*) created,
  3    COUNT(
  4    CASE
  5      WHEN a.approved_yn = 'Y'
  6      THEN 1
  7      ELSE NULL
  8    END) approved,
  9    nvl(b.cnt,0) unique_users
 10  FROM assignment a, 
 11       ( select r_paper_id, count(distinct assigner_id) cnt
 12         from ( select r_paper_id, assigner_id FROM assignment
 13                union 
 14                select r_paper_id, approver_id FROM assignment )
 15         group by r_paper_id ) b
 16  WHERE a.assign_date >= to_date('20-10-2012', 'dd-mm-yyyy')
 17    and b.r_paper_id (+) = a.r_paper_id
 18  GROUP BY a.r_paper_id, b.cnt 
 19  order by 1;
R_PAPER_ID    CREATED   APPROVED UNIQUE_USERS
---------- ---------- ---------- ------------
         1          3          2            3
         2          3          2            3

Note that you don't need research_paper table for your question(s), so I removed from the query.

Regards
Michel
Re: How to bypass putting select inside a count function in the following query ? [message #569161 is a reply to message #569150] Sun, 21 October 2012 13:34 Go to previous message
daudiam
Messages: 51
Registered: June 2011
Member
Thanks a lot. It really helped

[Updated on: Sun, 21 October 2012 13:34]

Report message to a moderator

Previous Topic: tz_offset shows different value for different database
Next Topic: How to get correct minute between two hours?
Goto Forum:
  


Current Time: Wed Jul 30 05:04:53 CDT 2014

Total time taken to generate the page: 0.12974 seconds