Paul, that's what I would have thought, as I believe what I want is a
count/group of the "dept_scm_table_id" correct? If you notice the second
example/try, I did this:
> >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 |
> >+----------------+-------------------+-------+
But it omits the "NULL" rep and also "Christopher Martinez". It seems to be
showing the correct COUNT of each dept_scm_table_id, but it is lumping all
the rep_names together rather than keeping them associated with their
individual count. I'm sure that's how mySQL is supposed to work, I just
dont know how to separate it out and get the query I want like this one:
> >+----------------------+-------------------+-------+
> >| 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 |
> >+----------------------+-------------------+-------+
> -----Original Message-----
> From: Paul DuBois [mailto:paul@stripped]
> Sent: Tuesday, April 29, 2003 4:36 PM
> To: Daevid Vincent; 'MySQL General Discussion (E-mail)'
> Subject: Re: Slight help with COUNT() and GROUP BY needed.
>
>
> 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
>
