From: Daevid Vincent Date: April 30 2003 1:59am Subject: RE: Slight help with COUNT() and GROUP BY needed. List-Archive: http://lists.mysql.com/mysql/138742 Message-Id: <000f01c30ebc$14f002a0$a50aa8c0@Locutus> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =3D scm_id AND dept_rep_table_id =3D '9' LEFT JOIN = rep_table ON rep_id =3D 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 =3D scm_id AND dept_rep_table_id =3D '1' LEFT JOIN = rep_table ON rep_id =3D 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 =3D scm_id LEFT JOIN rep_table ON rep_id =3D = 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]=20 > Sent: Tuesday, April 29, 2003 6:40 PM > To: 'MySQL General Discussion (E-mail)' > Subject: RE: Slight help with COUNT() and GROUP BY needed. >=20 >=20 > 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=20 > the second > example/try, I did this:=20 >=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; >=20 >=20 > > >+----------------+-------------------+-------+ > > >| rep_name | dept_scm_table_id | count | > > >+----------------+-------------------+-------+ > > >| Daevid Vincent | 80 | 2 | > > >| Daevid Vincent | 90 | 1 | > > >| Daevid Vincent | 20 | 3 | > > >| Chad Storey | 70 | 1 | > > >+----------------+-------------------+-------+ >=20 > But it omits the "NULL" rep and also "Christopher Martinez".=20 > It seems to be > showing the correct COUNT of each dept_scm_table_id, but it=20 > 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=20 > work, I just > don=92t know how to separate it out and get the query I want=20 > like this one: >=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 | > > >+----------------------+-------------------+-------+ >=20 >=20 > > -----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=20 > 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=20 > 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=20 > 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 >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: =20 > http://lists.mysql.com/mysql?> unsub=3Ddaevid@stripped >=20 >=20