SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting number of todos.

    Hey Guys
    I needed some help in writing a sql. I want to find number of todo per employee.
    This is how table looks like
    table: employee
    employee_ID
    Employee_name
    Employee_Location

    table: EemployeeTodo_rel
    employee_ID
    todo_ID

    table: todo
    todo_ID
    todo_subject

    What I want to do I want to list all the employee with number of task they have todo and with there name.Result should look like:
    Employee_name numberoftodo
    Umang 10

    I have already tried following:
    Code MySQL:
    SELECT DISTINCT
        employee.employee_name AS name
    	,COUNT(todo.todoID)
    FROM
        employee
    	LEFT JOIN employeetodorel
    	ON (employeetodorel.employeeId=employee.employee_id)
    	LEFT JOIN todo
    	ON (employeetodorel.toDoId=todo.todoID)
    what count function is doing is that it is displaying total number of todos!!.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by uniqueumang View Post
    Hey Guys
    I needed some help in writing a sql. I want to find number of todo per employee.
    This is how table looks like
    table: employee
    employee_ID
    Employee_name
    Employee_Location

    table: EemployeeTodo_rel
    employee_ID
    todo_ID

    table: todo
    todo_ID
    todo_subject
    First of all - does todo REALLY have to be it's own table? Are the chances different people have the exact same todo item worth having the extra table?

    But to answer your question for the existing structure as it is - your query is close as you posted it.

    Code MySQL:
    SELECT employee.employee_name AS name
    , COUNT(todo.todoID)
    FROM employee
    LEFT JOIN employeetodorel ON (employeetodorel.employeeId=employee.employee_id)
    LEFT JOIN todo ON (employeetodorel.toDoId=todo.todoID) 
    GROUP BY employee.employee_name

    Though with your structure and the fact that you want just a count, this will give you the same exact results and will run slightly faster as it's only joining two tables vs three.

    Code MySQL:
    SELECT employee.employee_name AS name
    , COUNT(employeetodorel.toDoId)
    FROM employee
    LEFT JOIN employeetodorel ON (employeetodorel.employeeId=employee.employee_id)
    GROUP BY employee.employee_name
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    First of all - does todo REALLY have to be it's own table? Are the chances different people have the exact same todo item worth having the extra table?

    But to answer your question for the existing structure as it is - your query is close as you posted it.

    Code MySQL:
    SELECT employee.employee_name AS name
    , COUNT(todo.todoID)
    FROM employee
    LEFT JOIN employeetodorel ON (employeetodorel.employeeId=employee.employee_id)
    LEFT JOIN todo ON (employeetodorel.toDoId=todo.todoID) 
    GROUP BY employee.employee_name

    Though with your structure and the fact that you want just a count, this will give you the same exact results and will run slightly faster as it's only joining two tables vs three.

    Code MySQL:
    SELECT employee.employee_name AS name
    , COUNT(employeetodorel.toDoId)
    FROM employee
    LEFT JOIN employeetodorel ON (employeetodorel.employeeId=employee.employee_id)
    GROUP BY employee.employee_name

    Thanks for help. I simplified the problem. Todo table contains fields like status which is inturn a enum, start date , duedate . What I intent to so is to find the percentage of overdue task (curdate() > todo.dueDate AND todo.status !='Completed') . Probably in future there is a possiblity of collabrated todos, but not at this stage. Hence I think it is worthwhile to keep todo a seperate table.
    Summary of todo table :
    Code MySQL:
    /*Column Information For - todo*/
    ----------------------------------------------
     
    Field          Type                                                                                Collation        Null    Key     Default      Extra           Privileges                       Comment
    -------------  ----------------------------------------------------------------------------------  ---------------  ------  ------  -----------  --------------  -------------------------------  -------
    todoID         int(11)                                                                             (NULL)           NO      PRI     (NULL)       auto_increment  select,insert,update,references         
    subject        varchar(50)                                                                         utf8_general_ci  NO              (NULL)                       select,insert,update,references         
    startDate      date                                                                                (NULL)           NO              (NULL)                       select,insert,update,references         
    dueDate        date                                                                                (NULL)           NO              (NULL)                       select,insert,update,references         
    status         enum('Not Started','In Progress','Completed','Waiting on Someone Else','Deferred')  utf8_general_ci  NO              Not Started                  select,insert,update,references         
    priority       enum('low','high','Normal')                                                         utf8_general_ci  NO              Normal                       select,insert,update,references         
    centCompleted  smallint(6)                                                                         (NULL)           NO              0                            select,insert,update,references         
    description    text                                                                                utf8_general_ci  YES             (NULL)                       select,insert,update,references         
     
    /*Index Information For - todo*/
    ---------------------------------------------
     
    Table   Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment
    ------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
    todo             0  PRIMARY              1  todoID       A                   27    (NULL)  (NULL)          BTREE              
     
    /*DDL Information For - todo*/
    -------------------------------------------
     
    Table   Create Table                                                                                                                   
    ------  -------------------------------------------------------------------------------------------------------------------------------
    todo    CREATE TABLE `todo` (                                                                                                          
              `todoID` int(11) NOT NULL AUTO_INCREMENT,                                                                                    
              `subject` varchar(50) NOT NULL,                                                                                              
              `startDate` date NOT NULL,                                                                                                   
              `dueDate` date NOT NULL,                                                                                                     
              `status` enum('Not Started','In Progress','Completed','Waiting on Someone Else','Deferred') NOT NULL DEFAULT 'Not Started',  
              `priority` enum('low','high','Normal') NOT NULL DEFAULT 'Normal',                                                            
              `centCompleted` smallint(6) NOT NULL DEFAULT '0',                                                                            
              `description` text,                                                                                                          
              PRIMARY KEY (`todoID`)                                                                                                       
            ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So far I this what I have done::
    Code MySQL:
    SELECT DISTINCT
        w.overdueNumber, w.name ,w.numberofTask ,(w.overdueNumber/w.numberofTask * '100') as percentage
    FROM (SELECT DISTINCT
        employee.employee_name AS name
    	,(SELECT count(todo.todoID) from todo,employeetodorel Where employee.employee_id=employeetodorel.employeeId AND employeetodorel.toDoId=todo.todoID AND todo.status!='Completed' AND CURDATE() > todo.dueDate  ) AS overdueNumber
    	,(SELECT count(todo.todoID) from todo,employeetodorel Where employee.employee_id=employeetodorel.employeeId AND employeetodorel.toDoId=todo.todoID AND todo.status!='Completed' ) AS numberofTask
    FROM employee) as w  ;

    I hope someone can help me in optimise the query.
    Last edited by uniqueumang; Feb 26, 2009 at 07:28. Reason: forgot something

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT w.employee_name AS name 
         , w.numberofTask 
         , w.overdueNumber
         , 100.0 * w.overdueNumber 
                 / w.numberofTask  AS percentage
      FROM (
           SELECT employee.employee_name
                , COUNT(CASE WHEN todo.dueDate < CURRENT_DATE
                             THEN 1 ELSE NULL END) 
                           AS overdueNumber
                , COUNT(*) AS numberofTask
             FROM employee
           INNER
             JOIN employeetodorel 
               ON employeetodorel.employeeId = employee.employee_id
           INNER
             JOIN todo
               ON todo.todoID = employeetodorel.toDoId
              AND todo.status <> 'Completed'
           GROUP
               BY employee.employee_name 
           ) AS w
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT w.employee_name AS name 
         , w.numberofTask 
         , w.overdueNumber
         , 100.0 * w.overdueNumber 
                 / w.numberofTask  AS percentage
      FROM (
           SELECT employee.employee_name
                , COUNT(CASE WHEN todo.dueDate < CURRENT_DATE
                             THEN 1 ELSE NULL END) 
                           AS overdueNumber
                , COUNT(*) AS numberofTask
             FROM employee
           INNER
             JOIN employeetodorel 
               ON employeetodorel.employeeId = employee.employee_id
           INNER
             JOIN todo
               ON todo.todoID = employeetodorel.toDoId
              AND todo.status <> 'Completed'
           GROUP
               BY employee.employee_name 
           ) AS w
    Thanks. I was going to reply how come you guys know so much about SQL. Then I realised you are author 'simply sql'.

    Thanks again.


Bookmarks

Posting Permissions

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