Quick SQL question

Question

I have 3 tables:

User (PK userid, ...)  
Computer (PK computerid, FK userid, FK cpuid, ...)   
CPU (PK cpuid, ...)

So, a user can have multiple computers, and each computer has exactly one CPU. Same cpu can appear in different computers.

I would like to get the number of computers and unique CPUs per user.

userid | Number of computers | Number of CPUs
---------------------------------------------
     1 | 3                   | 1     <- has 3 comps all with the same cpu
     2 | 13                  | 4     <- has 13 comps with 4 different cpus
...and so on

I dont have the problem with counting computers, but I stuck on counting the CPUs. Joining tables causes bad results because if the same cpu appears in more then one computer - count() returns total number of records as if they were not grouped...

Answer 1

You don't need a join, because you can count the IDs instead of using COUNT(*). In this case, your query would be:

SELECT userid, COUNT(DISTINCT computerid) AS Computers, COUNT(DISTINCT cpuid) AS CPUs
FROM Computer
GROUP BY userid

Answer 2

This is the most logical solution...

SELECT userid,
       (SELECT COUNT(*)
          FROM Computer
          WHERE userid = User.userid
       ) num_computers,
       (SELECT COUNT(*)
          FROM CPU
         WHERE EXISTS (
                SELECT *
                  FROM Computer
                 WHERE cpuid = CPU.cpuid
                   AND userid = User.userid
               )
       ) AS num_cpus
  FROM User

...but it is quite messy in SQL (and also possibly quite slow). The following is a more SQL-friendly arrangement:

SELECT userid,
       (SELECT COUNT(*)
          FROM Computer
          WHERE userid = User.userid
       ) num_computers,
       (SELECT COUNT(DISTINCT cpuid)
          FROM CPU
          JOIN Computer USING(cpuid)
         WHERE userid = User.userid
       ) AS num_cpus
  FROM User

Answer 3

Your tables are not normalised. You should make a associative entity between CPU and computer. Call it something like CompCpu with the pk of computer and cpu in it.

If you do that, you can inner join stuff easily. It should look roughly like....

SELECT u.userid, COUNT(c.computerid), COUNT(cpu.cpuid)
FROM User u
INNER JOIN Computer c ON u.userid = c.userid
INNER JOIN CompCpu cc ON c.computerid = cc.computerid
INNER JOIN CPU cpu ON cpu.cpuid = cc.cpuid
GROUP BY u.userid

Answer 4

this should cover all cases.. (even users without computers..)

SELECT 
  User.UserId,
  COUNT(Computer.ComputerId) AS [Computer #],
  COUNT(DISTINCT Computer.CpuId) AS [CPU #]
FROM 
  User
  LEFT OUTER JOIN
    Computer ON Computer.UserId = User.UserId
GROUP BY
  User.UserId

Answer 5

This will include users with or without computers and handles distinct CPU counting.

I replicated your SQL Tables as such

Schemas

Users

ID - Int (PK)
Name - Nvarchar(50)

Table CPUs

CPUID - Int (PK)
Name - Nvarchar(50)

Table Computers

CompID - Int(PK)
CPUID - Int(FK)
UserID - Int(FK)
Name - Nvarchar(50)

Data in Tables

Users

ID  Name
1   Tommy
2   Steve
3   Jeff

Computers

ID     UserID      CPUID     Name
1       1          1         Dell 1
2       1          1         Dell 2
3       1          1         Dell 3
4       2          3         Dell 4
5       2          3         Dell 5
6       2          4         Dell 6

CPUs

CPUID      Name
1         Intel 1
2         Intel 2
3         AMD 1
4         AMD 2

Query

SELECT     COUNT(DISTINCT Computers.CPUID) AS CPUs, COUNT(Computers.ComputerID)
AS numComputers, Users.Name
FROM         Computers RIGHT OUTER JOIN
                      Users ON Computers.UserID = Users.UserID
GROUP BY Users.Name

Results

CPUs     numComputers     Name
0          0               Jeff
2          3               Steve
1          3               Tommy

Answer 6

Try

select 
  [user].userid,
  COUNT(computerid) AS Computers,
  COUNT(distinct CpuID) AS CpuModels
from [user] left outer join computer on [user].userid=computer.userid
group by [user].userid

LEFT OUTER JOIN will return all Users but only matching computers. If a user has no computer it will return NULL values for the fields that come from Computer (ComputerID,CpuID). COUNT() doesn't count NULLs so you get 0 Computers from users without computers. You need DISTINCT CpuID to count distinct values of CpuID instead of all instances.