#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2016
    Posts
    109
    Rep Power
    4

    Mysql result in rows


    I been scratching my head for a while, I want to fetch result from the database where each month has data which is the down_time_spent.
    For each department I want to view the result as below.


    for example

    I am expecting something similar :

    Code:
    Department             January               February            March            ..etc
    
    PCB                    40                     50                     70               ..etc
    
    Dispatch                200                  50                      60                 ..etc  
    
    ..etc
    Here is the query I was trying to work with:
    Code:
            SELECT  d.department,
             MONTHNAME(date_created) as month,
             COALESCE(SUM(CASE WHEN c.cat_id IN (5,6,7,8,9,10,11,12,13,15) THEN time_spent END), 0) as down_time_spent
              FROM master as m
               INNER JOIN category as c ON c.cat_id = m.cat_id
               INNER JOIN department as d ON d.dept_id = m.dept_id
               GROUP By d.department
    Any help would be greatly appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Code:
    SELECT d.department
         , MONTHNAME(date_created) AS mth
         , SUM(CASE WHEN c.cat_id IN (5,6,7,8,9,10,11,12,13,15) 
                    THEN time_spent 
                    ELSE NULL END) AS down_time_spent
      FROM master AS m
    INNER 
      JOIN category AS c 
        ON c.cat_id = m.cat_id
    INNER 
      JOIN department AS d 
        ON d.dept_id = m.dept_id
    GROUP 
        BY d.department
         , mth
    this will give you up to 12 rows per department

    to spread all the months out on one line, use your application language (e.g. php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2016
    Posts
    109
    Rep Power
    4
    Originally Posted by r937
    Code:
    SELECT d.department
         , MONTHNAME(date_created) AS mth
         , SUM(CASE WHEN c.cat_id IN (5,6,7,8,9,10,11,12,13,15) 
                    THEN time_spent 
                    ELSE NULL END) AS down_time_spent
      FROM master AS m
    INNER 
      JOIN category AS c 
        ON c.cat_id = m.cat_id
    INNER 
      JOIN department AS d 
        ON d.dept_id = m.dept_id
    GROUP 
        BY d.department
         , mth
    this will give you up to 12 rows per department

    to spread all the months out on one line, use your application language (e.g. php)
    Thank you Mr Rudy. Its working now.

IMN logo majestic logo threadwatch logo seochat tools logo