List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:July 12 2004 8:48pm
Subject:RE: How can I count() on multiple tables in a single query? SOLVED
View as plain text  
Thanks for the reply Shawn. When I ran your version, I noticed that my
'devices' column was off by a multiplier of 3 (that is, it said 9 rather
than 3, or 15 rather than 5, etc.) Turns out, it just needed another
DISTINCT in there. So, for those playing along at home, here is the final
working query:

SELECT Departments.DeptID, 
	 DeptName, 
	 COUNT(DISTINCT UserDept.CoreID) AS users, 
	 COUNT(DISTINCT IP_Addr) as devices
FROM   Departments
	 LEFT JOIN IP_Dept
       ON Departments.DeptID = IP_Dept.DeptID
	 LEFT JOIN UserDept
       ON Departments.DeptID = UserDept.DeptID
GROUP BY Departments.DeptID, Departments.DeptName
ORDER BY DeptName DESC;

 

> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped] 
> Sent: Monday, July 12, 2004 6:39 AM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: How can I count() on multiple tables in a single query?
> 
> 
> It sounds as though you don't want to see "how many rows are 
> returned for
> the users column" but rather "how many distinct names are in 
> that column";
> you need a COUNT(DISTINCT). Your GROUP BY clause is also off 
> as it does not
> list all of the non-aggregated columns in your SELECT 
> statement. See if
> this works:
> 
> SELECT
>       Departments.DeptID
>       , DeptName
>       , COUNT(DISTINCT UserDept.CoreID) AS users
>       , COUNT(IP_Addr) as devices
> FROM  Departments
> LEFT JOIN IP_Dept
>       ON Departments.DeptID = IP_Dept.DeptID
> LEFT JOIN UserDept
>       ON Departments.DeptID = UserDept.DeptID
> GROUP BY Departments.DeptID, Departments.DeptName
> ORDER BY DeptName DESC;
> 
> Yours,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 
> 
> Using mysql v4.0.x on linux.
> 
> Given three tables...
> 
> CREATE TABLE Departments (
>   DeptID int(10) unsigned NOT NULL auto_increment,
>   DeptName char(30) default NULL,
>   PRIMARY KEY  (DeptID)
> )
> 
> CREATE TABLE UserDept (
>   CoreID int(10) unsigned NOT NULL default '0',
>   DeptID int(10) unsigned NOT NULL default '0',
>   DeptAdmin char(1) default NULL,
>   DeptEmail char(1) default NULL,
>   DeptContact char(1) default NULL,
>   KEY DeptID (DeptID),
>   KEY CoreID (CoreID)
> )
> 
> CREATE TABLE IP_Dept (
>   IP_Addr int(10) unsigned NOT NULL default '0',
>   DeptID int(10) unsigned NOT NULL default '0',
>   UNIQUE KEY DeptIP (IP_Addr,DeptID)
> )
> 
> What I want is a listing of all the department names, and a 
> tally of how
> many users in each, and another column with the tally of how 
> many IPs in each
> 
> I've tried various combinations of this, changing the COUNT() 
> and GROUP BY
> values:
> 
> SELECT
>              Departments.DeptID, DeptName, 
> COUNT(UserDept.CoreID) AS users,
> COUNT(IP_Addr) as devices
> FROM  Departments
>               LEFT JOIN IP_Dept on Departments.DeptID = IP_Dept.DeptID
>                LEFT JOIN UserDept ON Departments.DeptID = 
> UserDept.DeptID
> GROUP BY
>              UserDept.CoreID, IP_Dept.IP_Addr
> ORDER BY
>              DeptName DESC;
> 
> But nothing is working right. Mostly what happens is both 'users' and
> 'devices' is the same value. Is this possible? I can do it 
> for the first
> COUNT(). And then I could do a second query, but I'm trying 
> to do this in a
> single query if possible.

Thread
How can I count() on multiple tables in a single query?Daevid Vincent10 Jul
Re: How can I count() on multiple tables in a single query?SGreen12 Jul
  • RE: How can I count() on multiple tables in a single query? SOLVEDDaevid Vincent12 Jul