List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 29 2003 11:35pm
Subject:Re: Slight help with COUNT() and GROUP BY needed.
View as plain text  
At 20:46 -0700 4/28/03, Daevid Vincent wrote:
>Me again. Having a bit of trouble figuring out how COUNT and GROUP BY work.

Normally you count the thing you're grouping by.  Most of your queries
don't do that.

>
>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;


-- 
Paul DuBois
http://www.kitebird.com/
sql, query
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