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

This logic should work:

1. virtual table on table2: select max(ProcNum) group by ComputerID, ProcType
2. Join Table1(t1) with the virtual table(t2) count(t1.ComputerID), t1.OS, t2.num_Procs, t2.ProcType where t1.computerid=t2.computerid group by t1.OS, t2.num_Procs, t2.ProcType
   • Jusung Yang

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 Thu Oct 24 2002 - 21:02:07 CEST