SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Netherlands
    Posts
    172
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY more columns

    I have this table with rows of training activities:
    Code MySQL:
    id	training_date	training_activity
    1	2009-01-02		running
    2	2009-01-03		cycling
    3	2009-01-04		cycling
    4	2009-01-07		running
    5	2009-01-10		running
    6	2009-01-12		cycling
    7	2009-01-14		running
    8	2009-01-15		fitness
    9	2009-01-16		running
    10	2009-01-20		running
    What I want to extract from it is some sort of summary, like this:
    Code MySQL:
    wk		# run	# cycling	# fitness
    1		2		2			0
    2		2		1			0
    3 		2		0			1
    or, in PHP, an array with rows for each week and counts for the different training activities

    so far, I have come to this
    Code MySQL:
    SELECT
    	date_format(`training_date`, '%U') as Week,
     	`training_activity` as Training,  
      	ifnull(count(`training_activity`),0) as Amount
    FROM `trainings`
    WHERE `user_id` = 1
    GROUP BY 
       	`training_activity`,
      	date_format(`training_date`, '%U'),
    	date_format(`training_date`, '%Y')
    ORDER BY
    	date_format(`training_date`, '%Y') DESC, 
    	date_format(`training_date`, '%U') DESC
    LIMIT 52

    However, with a query like this I end up with a result like:

    Code MySQL:
    wk 1    running    3
    wk1     cycling     2
    etc

    Not what I want. Maybe I need to do something with inner joins. Any hints?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT DATE_FORMAT(training_date,'%Y wk%U') AS Week
         , COUNT(CASE WHEN training_activity = 'running' THEN 'ok' END) AS running
         , COUNT(CASE WHEN training_activity = 'cycling' THEN 'ok' END) AS cycling
         , COUNT(CASE WHEN training_activity = 'fitness' THEN 'ok' END) AS fitness
      FROM trainings
     WHERE user_id = 1
    GROUP 
        BY DATE_FORMAT(training_date,'%Y wk%U')
    ORDER
        BY DATE_FORMAT(training_date,'%Y wk%U') DESC LIMIT 52
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Netherlands
    Posts
    172
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Wow! I didn't expect such a quick and complete answer. Thank you. That's some code I can study and learn from. There's so much more possible in mysql then I knew...


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
  •