List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 29 2003 3:46am
Subject:Slight help with COUNT() and GROUP BY needed.
View as plain text  
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;

Thread
Slight help with COUNT() and GROUP BY needed.Daevid Vincent29 Apr
  • Re: Slight help with COUNT() and GROUP BY needed.Paul DuBois30 Apr
    • RE: Slight help with COUNT() and GROUP BY needed.Daevid Vincent30 Apr
      • RE: Slight help with COUNT() and GROUP BY needed.Daevid Vincent30 Apr
      • RE: Slight help with COUNT() and GROUP BY needed.Paul DuBois30 Apr