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

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