From: Daevid Vincent Date: April 29 2003 3:46am Subject: Slight help with COUNT() and GROUP BY needed. List-Archive: http://lists.mysql.com/mysql/138654 Message-Id: <001601c30e02$017cdeb0$a50aa8c0@Locutus> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable 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 =3D 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 =3D = rep_id=20 -> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100=20 -> GROUP BY dept_id=20 -> 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 =3D = rep_id=20 -> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100=20 -> GROUP BY dept_scm_table_id=20 -> 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 =3D = rep_id=20 -> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100=20 -> GROUP BY rep_name=20 -> 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 =3D = rep_id=20 -> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100=20 -> GROUP BY dept_rep_table_id=20 -> 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 =3D = rep_id=20 -> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100=20 -> GROUP BY dept_id =20 -> 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=3DInnoDB; 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=3DInnoDB; 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=3DInnoDB;