Re: How do i get a count of max items?

From: Craig <elliott_craig_at_hotmail.com>
Date: 24 Oct 2002 15:40:18 -0700
Message-ID: <4f71eb53.0210241440.41b18a54_at_posting.google.com>


Here is one way.

you can tune it, expand it, make it better.. ;-}

---****

create table test1 (cID number, cName varchar2(10), cOS varchar2(10)); commit;

insert into test1(cID,cName,cOS) values (12234,'CompA','NT');
insert into test1(cID,cName,cOS) values (12235,'CompB','2000');
insert into test1(cID,cName,cOS) values (12236,'CompC','2000');
insert into test1(cID,cName,cOS) values (12237,'CompD','98');
insert into test1(cID,cName,cOS) values (12238,'CompE','NT');
insert into test1(cID,cName,cOS) values (12239,'CompF','98');
insert into test1(cID,cName,cOS) values (12240,'CompG','2000');
commit;

create table test2 (pID number, cID number, pType varchar2(10), pNum number); commit;

insert into test2 (pID,cID,pType,pNum) values (12341,12234,'Intel',1);
insert into test2 (pID,cID,pType,pNum) values (12342,12235,'Intel',1);
insert into test2 (pID,cID,pType,pNum) values (12343,12235,'Intel',2);
insert into test2 (pID,cID,pType,pNum) values (12344,12236,'Intel',1);
insert into test2 (pID,cID,pType,pNum) values (12345,12237,'Intel',1);
insert into test2 (pID,cID,pType,pNum) values (12346,12238,'RISC',1);
insert into test2 (pID,cID,pType,pNum) values (12347,12238,'RISC',2);
insert into test2 (pID,cID,pType,pNum) values (12348,12238,'RISC',3);
insert into test2 (pID,cID,pType,pNum) values (12349,12239,'Intel',1);
insert into test2 (pID,cID,pType,pNum) values (12350,12240,'Intel',1);
insert into test2 (pID,cID,pType,pNum) values (12351,12240,'Intel',2);
commit;

17:08:56 SQL> select * from test1;

       CID CNAME COS
---------- ---------- ----------

     12234 CompA      NT
     12235 CompB      2000
     12236 CompC      2000
     12237 CompD      98
     12238 CompE      NT
     12239 CompF      98
     12240 CompG      2000

7 rows selected.

Elapsed: 00:00:00.15
17:09:02 SQL> select * from test2;

       PID CID PTYPE PNUM ---------- ---------- ---------- ----------

     12341      12234 Intel               1
     12342      12235 Intel               1
     12343      12235 Intel               2
     12344      12236 Intel               1
     12345      12237 Intel               1
     12346      12238 RISC                1
     12347      12238 RISC                2
     12348      12238 RISC                3
     12349      12239 Intel               1
     12350      12240 Intel               1
     12351      12240 Intel               2

11 rows selected.

select count(dummy) "# of Machines", cos "OS", pCnt "#Procs", ptype "ProcType" from (
  select 1 dummy, t2.ptype, max(t2.pnum) pCnt , t1.cos   from test2 t2

     , test1 t1
  where t1.cid = t2.cid
  group by t2.cid, t2.ptype, t1.cos
)
group by ptype, pCnt, cos;

# of Machines OS #Procs ProcType ------------- ---------- ---------- ----------

            1 2000                1 Intel
            2 98                  1 Intel
            1 NT                  1 Intel
            2 2000                2 Intel
            1 NT                  3 RISC

Elapsed: 00:00:00.16
17:38:03 SQL>
;

.Craig.

---****

raedwa01_at_hotmail.com (Rob Edwards) wrote in message news:<8ea2ecb0.0210240443.342abc22_at_posting.google.com>...

> I have two tables with a layout like this.
> 
> Table1:
> ComputerID  ComputerName   OS
> 12234           CompA      NT
> 12235           CompB      2000
> 12236           CompC      2000
> 12237           CompD      98
> 12238           CompE      NT
> 12239           CompF      98
> 12240           CompG      2000
> 
> Table2:
> ProcID      ComputerID     ProcType   ProcNum
> 12341         12234         Intel        1
> 12342         12235         Intel        1  
> 12343         12235         Intel        2    
> 12344         12236         Intel        1    
> 12345         12237         Intel        1  
> 12346         12238         RISC         1  
> 12347         12238         RISC         2  
> 12348         12238         RISC         3  
> 12349         12239         Intel        1  
> 12350         12240         Intel        1  
> 12351         12240         Intel        2  
> 
> 
> I need to get a query that gives me something like this:
> 
> # of Machines    OS       #Procs     ProcType
> 1                NT        1          Intel
> 1                NT        3          RISC
> 1                2000      1          Intel
> 2                2000      2          Intel
> 2                98        1          Intel
> 
> 
> I tried something like:
> Select Count(MachineID), OS, Max(NumProcs), ProcType
> 
> But that gives me 
> 
> 1                NT        1          Intel
> 1                NT        3          RISC
> 3                2000      2          Intel
> 2                98        1          Intel
> 
> Notice that the 2000 machine comes back as 3 having 2 processors, when
> there are 2 that have 2 processors and 1 with 1.
> 
> Any idea how to get a count of machines and the number of processors
> in those machines?
> 
> Thanks,
> Rob
Received on Fri Oct 25 2002 - 00:40:18 CEST

Original text of this message