Results 1 to 4 of 4
  1. #1

    Optimal query for count and select

    I've got two tables:

    [person]
    -PersonId (int)
    -Name (varchar)
    -Type (int)
    -Status (int)

    and

    [status]
    -Status (int)
    -Description (varchar)

    What I want:
    - Get for every Status of [status], which is unique, the number of persons matching that particular status, de Status itself and the Description. If possible, I want to limit this action to a given Type.

  2. #2
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Since you didn't specify what RDBMS you were using, I'm going to assume MySQL.

    (One Query)

    Untested, and probably won't work, but atleast it gives you something work with:

    SELECT DISTINCT status.Status, count(person.PersonId) as total_p, status.Description FROM status, person WHERE status.Status = person.Status;

    Here is the way to do it using two queries (PHP):

    PHP Code:
    function parse($input)
    {
       
    $ret '(\'';
       while(list(,
    $val) = each($input))
       {
         
    $ret .= $val."','";
       }
       
    $ret substr($ret,0,strlen($ret)-2);
       
    $ret .= ')';
       return 
    $ret;
    }
    $list mysql_fetch_assoc(mysql_query("SELECT DISTINCT status FROM Status"));

    $num_persons mysql_num_rows(mysql_query("SELECT PersonId FROM person WHERE Status IN ".$list)); 
    Here would be the way to do it if your MySQL server supports subselects (4.1+)

    SELECT count(PersonId) AS num_persons WHERE Status = (SELECT DISTINCT Status FROM status);
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  3. #3
    Thank you. The query will not work, since the hard linking between status and person will cause a no-row instead of a zero, when there are no people with that particular status.

    It's too bad I'm stuck with MySQL 3.23...

    I think I rephrase the problem.

    Two tabels (person en status).

    [person]
    -PersonID (int)
    -Type (int)
    -Name (varchar)
    -Status (int)

    [status]
    -Status (int)
    -Description (varchar)

    How do I get the next output:

    -status.Status
    -status.Description
    -number of persons with this particular status

    Case:
    I have in my table [status] 3 records with Status as 10, 20 and 30
    Secondly, I have 10 persons in [person].
    7 persons have -Status = 10
    3 persons have -Status = 20
    0 persons have -Status = 30

    How do I get the following output?

    Record 1
    ---------
    Status = 10
    Description = 10-description
    Count_p = 7
    ---------

    Record 2
    ---------
    Status = 20
    Description = 20-description
    Count_p = 3
    ---------

    Record 3
    ---------
    Status = 30
    Description = 30-description
    Count_p = 0
    ---------

    Additionally / Optionally
    And how can I narrow the output with a given Type of Status?

  4. #4
    Join Date
    Feb 2003
    Location
    Virginia
    Posts
    237
    Code:
    mysql> SELECT DISTINCT status.Status, count(person.PersonId) as total_p, status.
    Description FROM person right join status on status.status = person.status group by status.status;
    +--------+---------+---------------+
    | Status | total_p | Description   |
    +--------+---------+---------------+
    |      1 |       7 | 1-description |
    |      2 |       3 | 2-description |
    |      3 |       0 | 3-description |
    +--------+---------+---------------+
    3 rows in set (0.00 sec)
    Protollix - Sean Finkel
    Email
    Website
    Shared Hosting :: Cloud Hosting

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •