SQL - Get departments and headcount

jnuneznyc

How can I get list of DISTINCT Departments and a count of each member in the department using just one query.

Assume a simple table structure

Users Table
UserID
Username
Password
Department
LastLogin

jmarco

I haven't tried or tested it, but I think that's probably similar to what you want to do. Maybe someone else can clean it up some more.

$department('dept1', 'dept2', 'dept3');

for ($i = 0; $i < $arraysize; $i++) {

  $query = "SELECT * FROM table WHERE department = ".$department[$i].";";

  $result = mysql_query($query, $connection) or die($die);

  mysql_count_rows($result);

  echo $statements;

}

BuzzLY

I think this will work. I am no expert on SQL yet, so I give no warranties 🙂

SELECT Department, count(*) as Numusers FROM Users GROUP BY Department

Does this look correct to you SQL experts out there?

jnuneznyc

SELECT Department, count(*) as Numusers FROM Users GROUP BY Department

Thanks! This seems to work!

BuzzLY

Woo Hoo!!! I guess I'm not as SQL-stupid as I thought I was :p

djBuilder

You can just say SELECT DISTINCT also.

SELECT DISTINCT Department, count(*) as Numusers FROM Users

This will only select DISTINCT Departments, but in this case the GROUP BY will also acheive the same thing.

BuzzLY

Are you sure that will work? DISTINCT tells MySQL to remove duplicate records. Are you sure count(*) will still return the number of duplicate records for each department?

djBuilder

Good point, I wasn't sure on the count(*) with MySQL's DISTINCT. I was just showing that you can do SELECT DISTINCT department. 😉