Me again. Having a bit of trouble figuring out how COUNT and GROUP BY work.
I have tried the following, but none are quite right. I am trying to get a
table like this, where I see each salesman has a count for each deptartments
that are at a given SCM level (scm_id). It can be the case that a rep is
deleted and so the dept_rep_table_id = NULL (like the first one). I know my
example is kinda lame in the count field, as most are '1' except for Chris's
'2' @ 20%...
+----------------------+-------------------+-------+
| rep_name | dept_scm_table_id | count |
+----------------------+-------------------+-------+
| NULL | 80 | 1 |
| Daevid Vincent | 90 | 1 |
| Daevid Vincent | 80 | 1 |
| Daevid Vincent | 20 | 1 |
| Chad Storey | 70 | 1 |
| Christopher Martinez | 20 | 2 |
+----------------------+-------------------+-------+
[Relevant tables at the end of this email...]
Here's what I've tried and all are just a slightly bit wrong, either they
omit reps, scms or the count is wrong...
mysql> SELECT CONCAT(rep_fname, ' ', rep_lname) AS rep_name,
dept_scm_table_id, COUNT(dept_scm_table_id) AS count
-> FROM dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id
-> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100
-> GROUP BY dept_id
-> ORDER BY rep_id;
+----------------------+-------------------+-------+
| rep_name | dept_scm_table_id | count |
+----------------------+-------------------+-------+
| NULL | 80 | 1 |
| Daevid Vincent | 90 | 1 |
| Daevid Vincent | 80 | 1 |
| Daevid Vincent | 20 | 1 |
| Chad Storey | 70 | 1 |
| Christopher Martinez | 20 | 1 |
| Christopher Martinez | 20 | 1 |
+----------------------+-------------------+-------+
7 rows in set (0.00 sec)
mysql> SELECT CONCAT(rep_fname, ' ', rep_lname) AS rep_name,
dept_scm_table_id, COUNT(dept_scm_table_id) AS count
-> FROM dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id
-> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100
-> GROUP BY dept_scm_table_id
-> ORDER BY rep_id;
+----------------+-------------------+-------+
| rep_name | dept_scm_table_id | count |
+----------------+-------------------+-------+
| Daevid Vincent | 80 | 2 |
| Daevid Vincent | 90 | 1 |
| Daevid Vincent | 20 | 3 |
| Chad Storey | 70 | 1 |
+----------------+-------------------+-------+
4 rows in set (0.00 sec)
mysql> SELECT CONCAT(rep_fname, ' ', rep_lname) AS rep_name,
dept_scm_table_id, COUNT(dept_scm_table_id) AS count
-> FROM dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id
-> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100
-> GROUP BY rep_name
-> ORDER BY rep_id;
+----------------------+-------------------+-------+
| rep_name | dept_scm_table_id | count |
+----------------------+-------------------+-------+
| NULL | 80 | 1 |
| Daevid Vincent | 20 | 3 |
| Chad Storey | 70 | 1 |
| Christopher Martinez | 20 | 2 |
+----------------------+-------------------+-------+
4 rows in set (0.01 sec)
mysql> SELECT CONCAT(rep_fname, ' ', rep_lname) AS rep_name,
dept_scm_table_id, COUNT(rep_id) AS count
-> FROM dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id
-> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100
-> GROUP BY dept_rep_table_id
-> ORDER BY rep_id;
+----------------------+-------------------+-------+
| rep_name | dept_scm_table_id | count |
+----------------------+-------------------+-------+
| NULL | 80 | 0 |
| Daevid Vincent | 20 | 3 |
| Chad Storey | 70 | 1 |
| Christopher Martinez | 20 | 2 |
+----------------------+-------------------+-------+
4 rows in set (0.00 sec)
mysql> SELECT CONCAT(rep_fname, ' ', rep_lname) AS rep_name,
dept_scm_table_id, COUNT(rep_id) AS count
-> FROM dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id
-> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100
-> GROUP BY dept_id
-> ORDER BY rep_id;
+----------------------+-------------------+-------+
| rep_name | dept_scm_table_id | count |
+----------------------+-------------------+-------+
| NULL | 80 | 0 |
| Daevid Vincent | 90 | 1 |
| Daevid Vincent | 80 | 1 |
| Daevid Vincent | 20 | 1 |
| Chad Storey | 70 | 1 |
| Christopher Martinez | 20 | 1 |
| Christopher Martinez | 20 | 1 |
+----------------------+-------------------+-------+
7 rows in set (0.00 sec)
---------- table schema -------------
CREATE TABLE `scm_table` (
`scm_id` tinyint(3) unsigned NOT NULL auto_increment,
`scm_name` varchar(255) NOT NULL default '',
PRIMARY KEY (`scm_id`)
) TYPE=InnoDB;
INSERT INTO `scm_table` VALUES (10,'Cold Lead');
INSERT INTO `scm_table` VALUES (20,'Interested Party');
INSERT INTO `scm_table` VALUES (40,'Sent Information, Online Demo, Eval
Sent, Onsite Meeting');
INSERT INTO `scm_table` VALUES (60,'Technically Approved: Eval Successful');
INSERT INTO `scm_table` VALUES (70,'Quote/Proposal Sent');
INSERT INTO `scm_table` VALUES (80,'Budget Approved');
INSERT INTO `scm_table` VALUES (90,'Contract Emailed/Invoice Sent');
INSERT INTO `scm_table` VALUES (100,'Closed Contract/Funds Recieved');
CREATE TABLE dept_table (
dept_id mediumint(8) unsigned NOT NULL auto_increment,
dept_timestamp timestamp(14) NOT NULL,
dept_incept date NOT NULL default '0000-00-00',
dept_company_table_id mediumint(8) unsigned NOT NULL default '0',
dept_rep_table_id smallint(8) unsigned default NULL,
dept_name varchar(255) NOT NULL default '',
dept_scm_table_id tinyint(3) unsigned NOT NULL default '10',
dept_SCM_10 date NOT NULL default '0000-00-00',
dept_SCM_20 date NOT NULL default '0000-00-00',
dept_SCM_40 date NOT NULL default '0000-00-00',
dept_SCM_60 date NOT NULL default '0000-00-00',
dept_SCM_70 date NOT NULL default '0000-00-00',
dept_SCM_80 date NOT NULL default '0000-00-00',
dept_SCM_90 date NOT NULL default '0000-00-00',
dept_SCM_100 date NOT NULL default '0000-00-00',
dept_notes text,
PRIMARY KEY (dept_id),
KEY dept_company_table_id (dept_company_table_id),
KEY dept_rep_table_id (dept_rep_table_id),
KEY dept_scm_table_id (dept_scm_table_id),
FOREIGN KEY (`dept_company_table_id`) REFERENCES `crm.company_table`
(`company_id`) ON DELETE CASCADE,
FOREIGN KEY (`dept_rep_table_id`) REFERENCES `crm.rep_table` (`rep_id`) ON
DELETE SET NULL
) TYPE=InnoDB;
CREATE TABLE rep_table (
rep_id smallint(5) unsigned NOT NULL auto_increment,
rep_login varchar(15) NOT NULL default '',
rep_password varchar(15) NOT NULL default '',
rep_fname varchar(20) NOT NULL default '',
rep_lname varchar(20) NOT NULL default '',
rep_notes longtext,
PRIMARY KEY (rep_id),
UNIQUE KEY rep_login (rep_login),
KEY rep_logpass (rep_login,rep_password)
) TYPE=InnoDB;
