OraFAQ Forum: SQL & PL/SQL » Group and Count Query

Home » SQL & PL/SQL » SQL & PL/SQL » Group and Count Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group and Count Query [message #6459]

Fri, 18 April 2003 10:25

Michele
Messages: 77
Registered: December 2000

Member

Hello,

I have the below query:

select c.LAST_NAME as Name, count(distinct s.pers_id)
from cntrctr c, svc_date s
where c.rep_status = '1' and c.territory = s.territory
group by c.LAST_NAME;

Name Count
Duck 10
Mouse 5

This basically gives me a count of a persons in the svc_date table that report to each manager in the cntrctr table.

What I need to add is a 3rd column that counts the number of persons that have a certain status. IE:

select count(*) from cntrctr,svc_date where cntrctr.territory = svc_date.territory and cntrctr.rep_status = '2';

Name Count Count2
Duck 10 9
Mouse 5 2

I would like to add the second code into the first query and have one query but I am having trouble.

Thanks
Michele

Report message to a moderator

Re: Group and Count Query [message #6460 is a reply to message #6459]

Fri, 18 April 2003 10:44

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Try

SQL> SELECT * FROM cntrctr;
  
LAST_ R  TERRITORY
----- - ----------
Duck  1       1007
Mouse 1       1008
Duck  2       1009
Mouse 2       1010
  
SQL> SELECT * FROM svc_date;
  
   PERS_ID  TERRITORY
---------- ----------
       101       1007
       102       1007
       103       1007
       104       1007
       105       1007
       106       1007
       107       1007
       108       1007
       109       1007
       110       1007
       151       1008
       152       1008
       153       1008
       154       1008
       155       1008
       201       1009
       202       1009
       203       1009
       204       1009
       205       1009
       206       1009
       207       1009
       208       1009
       209       1009
       251       1010
       252       1010
  
26 rows selected.
  
SQL> SELECT   c.last_name                       name
  2  ,        SUM(DECODE(c.rep_status,'1',1,0)) rep_status_1
  3  ,        SUM(DECODE(c.rep_status,'2',1,0)) rep_status_2
  4  FROM     cntrctr    c
  5  ,        svc_date   s
  6  WHERE    c.territory = s.territory
  7  GROUP BY c.last_name
  8  /
  
NAME  REP_STATUS_1 REP_STATUS_2
----- ------------ ------------
Duck            10            9
Mouse            5            2
  
SQL>

Good luck,

A

Report message to a moderator

Re: Group and Count Query [message #6462 is a reply to message #6459]

Fri, 18 April 2003 11:19

Michele
Messages: 77
Registered: December 2000

Member

Hi Art,

Thanks for your quick reply but I should have been more clear.

CNTRCTR
ID Name REP_STATUS Territory
1 Duck 1 05
2 Mouse 1 06
3 Goofy 2 05
4 Minney 2 06

SVC_DATE

ID TERRITORY
1 05
2 05
3 06
4 06

Rep_status 1 is the master record and then I need all the people who are rep_status '2' and are in the master territory

NAME COUNT(includes Master) Count(excludes Master)
DUCK 2 1
MOUSE 2 1

Sorry for the misunderstanding

Thanks Michele

Report message to a moderator

Re: Group and Count Query [message #6470 is a reply to message #6459]

Sat, 19 April 2003 04:34

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

SQL> SELECT * FROM cntrctr
  2  /

        ID LAST_NAME  REP_STATUS TERRITORY
---------- --------- ----------- ---------
         1 Duck                1 05
         2 Mouse               1 06
         3 Goofy               2 05
         4 Minney              2 06

SQL> SELECT   c1.last_name                           AS name,
  2           COUNT (c.id)                           AS "COUNT (includes Master)",
  3           SUM (DECODE (c.rep_status, '2', 1, 0)) AS "COUNT (excludes Master)"
  4  FROM     cntrctr c1,
  5           cntrctr c
  6  WHERE    c1.territory = c.territory
  7  AND      c1.rep_status = '1'
  8  GROUP BY c1.last_name
  9  /

NAME   COUNT (includes Master) COUNT (excludes Master)
------ ----------------------- -----------------------
Duck                         2                       1
Mouse                        2                       1

Report message to a moderator

Re: Group and Count Query [message #6485 is a reply to message #6459]

Mon, 21 April 2003 07:59

Michele
Messages: 77
Registered: December 2000

Member

Thank you to all who replied. Your solutions helped me a great deal.

Michele

Report message to a moderator

Previous Topic:	difference between is and as in procedure/function creation
Next Topic:	count is very slow--- most urgent !!!!!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:10:40 CDT 2024