List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 30 2003 2:19am
Subject:RE: Slight help with COUNT() and GROUP BY needed.
View as plain text  
At 18:39 -0700 4/29/03, Daevid Vincent wrote:
>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.

COUNT(*) counts NULL values, and is the only aggregate function to do so.
COUNT(expr) does not count NULL values.

The query isn't "losing" Christopher Martinez, but it's violating another
principle off using grouping -- when you group on columns, you can only
select for display the grouped-by columns and summary values (like COUNT)
calculated from them.  If you select other columns, MySQL will just pick
some value from the column for each group and display that.

If you want a count per combination of rep_name and dept_scm_table_id,
including NULL values, then SELECT rep_name, dept_scm_table_id, COUNT(*)
FROM ... GROUP BY rep_name, dept_scm_table_id;

>  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)
>>  >
>  > >


-- 
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