List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 30 2003 1:59am
Subject:RE: Slight help with COUNT() and GROUP BY needed.
View as plain text  
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
> don’t 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
> 
> 

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