Home » SQL & PL/SQL » SQL & PL/SQL » QUERY FOR GROUPING AND COUNT (11G)
QUERY FOR GROUPING AND COUNT [message #444925] Wed, 24 February 2010 17:44 Go to next message
asksrini
Messages: 2
Registered: February 2010
Location: San Mateo
Junior Member
TASK_ID, TASK_STATUS, TASK_OWNER
================================
00001 , OPEN , ABC
00002 , OPEN , XYZ
00003 , WIP , ABC
00004 , CLOSED , XYZ
00005 , WIP , XYZ
00006 , CLOSED , XYZ
00007 , OPEN , XYZ


Output Required

Owner , Open , WIP, Closed
ABC 1 1 0
XYZ 2 1 2
Re: QUERY FOR GROUPING AND COUNT [message #444926 is a reply to message #444925] Wed, 24 February 2010 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: QUERY FOR GROUPING AND COUNT [message #444927 is a reply to message #444926] Wed, 24 February 2010 18:30 Go to previous messageGo to next message
asksrini
Messages: 2
Registered: February 2010
Location: San Mateo
Junior Member
1. Create Table using the script

create table TASK_MASTER (
TASK_ID NUMBER,
TASK_OWNER VARCHAR2(50),
TASK_STATUS VARCHAR2(10)
)

2. Insert the data

INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00001 , 'OPEN' , 'ABC');
INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00002 , 'OPEN' , 'XYZ');
INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00003 , 'WIP' , 'ABC');
INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00004 , 'CLOSED' , 'XYZ');
INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00005 , 'WIP' , 'XYZ');
INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00006 , 'CLOSED' , 'XYZ');
INSERT INTO TASK_MASTER (TASK_ID,TASK_STATUS,TASK_OWNER) VALUES (00007 , 'OPEN' , 'XYZ');
COMMIT;

3. Output Required
=================

TASK_OWNER , OPEN , WIP, CLOSED
ABC 1 1 0
XYZ 2 1 2


Requirement : To display # of tasks for each owner in various statuses.
Re: QUERY FOR GROUPING AND COUNT [message #444951 is a reply to message #444927] Wed, 24 February 2010 22:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
First of all you create inner query with three more columns, OPEN, WIP, CLOSED
like for OPEN
case task_status when 'OPEN' then 1 else 0 end OPEN

and same for WIP and CLOSED

Then in outer query, use GROUP BY task_owner and SUM(OPEN), SUM(WIP), SUM(CLOSED).

regards,
Delna
Re: QUERY FOR GROUPING AND COUNT [message #444960 is a reply to message #444927] Wed, 24 February 2010 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select task_owner,
  2         count(decode(task_status,'OPEN',1)) open,
  3         count(decode(task_status,'WIP',1)) wip,
  4         count(decode(task_status,'CLOSED',1)) closed
  5  from task_master
  6  group by task_owner
  7  /
TASK_OWNER       OPEN        WIP     CLOSED
---------- ---------- ---------- ----------
ABC                 1          1          0
XYZ                 2          1          2


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: QUERY FOR GROUPING AND COUNT [message #444963 is a reply to message #444925] Wed, 24 February 2010 23:29 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Oh I think I took it bit further..

WITH DATA AS (
SELECT  TASK_OWNER, CASE WHEN TASK_STATUS = 'OPEN' THEN 1 ELSE 0 END "OPEN",
CASE WHEN TASK_STATUS = 'WIP' THEN 1 ELSE 0 END "WIP",
CASE WHEN TASK_STATUS = 'CLOSED' THEN 1 ELSE 0 END "CLOSED" FROM TASK_MASTER
 )
SELECT TASK_OWNER , 
	NVL((SELECT COUNT(OPEN) FROM DATA A WHERE OPEN = 1 AND A.TASK_OWNER = DATA.TASK_OWNER GROUP BY  A.TASK_OWNER ),0) OPEN, 
	NVL((SELECT COUNT(WIP) FROM DATA C WHERE WIP = 1 AND C.TASK_OWNER = DATA.TASK_OWNER GROUP BY  C.TASK_OWNER ),0) WIP, 
	NVL((SELECT COUNT(CLOSED) FROM DATA C WHERE CLOSED = 1 AND C.TASK_OWNER = DATA.TASK_OWNER GROUP BY  C.TASK_OWNER ),0) CLOSED
FROM DATA
GROUP BY TASK_OWNER ORDER BY TASK_OWNER
/


TASK_OWNER                                               OPEN        WIP     CLOSED
-------------------------------------------------- ---------- ---------- ----------
ABC                                                         1          1          0
XYZ                                                         2          1          2

Re: QUERY FOR GROUPING AND COUNT [message #444966 is a reply to message #444963] Wed, 24 February 2010 23:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Wow!!!
Query with great performance...

regards,
Delna
Re: QUERY FOR GROUPING AND COUNT [message #444973 is a reply to message #444966] Thu, 25 February 2010 00:14 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
delna.sexy wrote on Wed, 24 February 2010 23:58
Wow!!!
Query with great performance...

regards,
Delna


I know... and I already said after looking @ Michel Query

Quote:
Oh I think I took it bit further..


but since I put in my efforts so Posted it.. Cool
Re: QUERY FOR GROUPING AND COUNT [message #444976 is a reply to message #444973] Thu, 25 February 2010 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but since I put in my efforts so Posted it..

Next time format it as it does not exceed 80 characters per line.

Regards
Michel
Re: QUERY FOR GROUPING AND COUNT [message #444977 is a reply to message #444925] Thu, 25 February 2010 00:41 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ok. My bad there.

[Updated on: Thu, 25 February 2010 00:42]

Report message to a moderator

Previous Topic: update query
Next Topic: group by problem
Goto Forum:
  


Current Time: Sun Dec 04 22:47:36 CST 2016

Total time taken to generate the page: 0.15967 seconds