Re: How do i get a count of max items?
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, > RobReceived on Fri Oct 25 2002 - 00:40:18 CEST