I can get this to work for a single rep, but not a table of ALL of them.
Notice the third example here is not what I want.
mysql> SELECT COUNT(dept_scm_table_id) as count, scm_id, CONCAT(rep_fname,'
', rep_lname) AS rep_name FROM scm_table INNER JOIN dept_table ON
dept_scm_table_id = scm_id AND dept_rep_table_id = '9' LEFT JOIN rep_table
ON rep_id = dept_rep_table_id GROUP BY scm_id ORDER BY scm_id DESC;
+-------+--------+----------------------+
| count | scm_id | rep_name |
+-------+--------+----------------------+
| 2 | 20 | Christopher Martinez |
| 18 | 10 | Christopher Martinez |
+-------+--------+----------------------+
2 rows in set (0.01 sec)
mysql> SELECT COUNT(dept_scm_table_id) as count, scm_id, CONCAT(rep_fname,'
', rep_lname) AS rep_name FROM scm_table INNER JOIN dept_table ON
dept_scm_table_id = scm_id AND dept_rep_table_id = '1' LEFT JOIN rep_table
ON rep_id = dept_rep_table_id GROUP BY scm_id ORDER BY scm_id DESC;
+-------+--------+----------------+
| count | scm_id | rep_name |
+-------+--------+----------------+
| 1 | 90 | Daevid Vincent |
| 1 | 80 | Daevid Vincent |
| 1 | 20 | Daevid Vincent |
+-------+--------+----------------+
mysql> SELECT COUNT(dept_scm_table_id) as count, scm_id, CONCAT(rep_fname,'
', rep_lname) AS rep_name FROM scm_table INNER JOIN dept_table ON
dept_scm_table_id = scm_id LEFT JOIN rep_table ON rep_id = dept_rep_table_id
GROUP BY scm_id ORDER BY scm_id DESC;
+-------+--------+----------------+
| count | scm_id | rep_name |
+-------+--------+----------------+
| 1 | 90 | Daevid Vincent |
| 2 | 80 | Daevid Vincent |
| 1 | 70 | Chad Storey |
| 3 | 20 | Daevid Vincent |
| 50 | 10 | Chad Storey |
+-------+--------+----------------+
5 rows in set (0.00 sec)
> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Tuesday, April 29, 2003 6:40 PM
> To: 'MySQL General Discussion (E-mail)'
> Subject: RE: Slight help with COUNT() and GROUP BY needed.
>
>
> 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
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=daevid@stripped
>
>
