Home -> Usenet -> comp.databases.oracle -> How do i get a count of max items?

How do i get a count of max items?

From: Rob Edwards <raedwa01_at_hotmail.com>
Date: 24 Oct 2002 05:43:20 -0700
Message-ID: <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 Thu Oct 24 2002 - 14:43:20 CEST












Original text of this message