From: Daevid Vincent Date: April 30 2003 1:39am Subject: RE: Slight help with COUNT() and GROUP BY needed. List-Archive: http://lists.mysql.com/mysql/138739 Message-Id: <000b01c30eb9$63e07960$a50aa8c0@Locutus> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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:=20 > >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=20 > dept_rep_table_id =3D 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 don=92t 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]=20 > 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. >=20 >=20 > At 20:46 -0700 4/28/03, Daevid Vincent wrote: > >Me again. Having a bit of trouble figuring out how COUNT and=20 > GROUP BY work. >=20 > Normally you count the thing you're grouping by. Most of your queries > don't do that. >=20 > > > >I have tried the following, but none are quite right. I am=20 > trying to get a > >table like this, where I see each salesman has a count for=20 > each deptartments > >that are at a given SCM level (scm_id). It can be the case=20 > that a rep is > >deleted and so the dept_rep_table_id =3D NULL (like the first=20 > one). I know my > >example is kinda lame in the count field, as most are '1'=20 > 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=20 > 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=20 > dept_rep_table_id =3D 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=20 > dept_rep_table_id =3D 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=20 > dept_rep_table_id =3D 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=20 > dept_rep_table_id =3D 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=20 > dept_rep_table_id =3D rep_id > > -> WHERE dept_scm_table_id > 10 AND dept_scm_table_id < 100 > > -> GROUP BY dept_id=A0 > > -> 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=20 > Demo, Eval > >Sent, Onsite Meeting'); > >INSERT INTO `scm_table` VALUES (60,'Technically Approved:=20 > 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=20 > 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=20 > `crm.company_table` > >(`company_id`) ON DELETE CASCADE, > > FOREIGN KEY (`dept_rep_table_id`) REFERENCES=20 > `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; >=20 >=20 > --=20 > Paul DuBois > http://www.kitebird.com/ > sql, query >=20