List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 12 2004 1:39pm
Subject:Re: How can I count() on multiple tables in a single query?
View as plain text  
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




|---------+---------------------------->
|         |           "Daevid Vincent" |
|         |           <daevid@stripped|
|         |           m>               |
|         |                            |
|         |           07/09/2004 07:22 |
|         |           PM               |
|         |                            |
|---------+---------------------------->
 
>--------------------------------------------------------------------------------------------------------------------------------|
  |                                                                                       
                                        |
  |       To:       <mysql@stripped>                                         
                                              |
  |       cc:                                                                             
                                        |
  |       Fax to:                                                                         
                                        |
  |       Subject:  How can I count() on multiple tables in a single query?               
                                        |
 
>--------------------------------------------------------------------------------------------------------------------------------|




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.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1






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