MYSQL - group by, count and sum problem

[murfitUK]

I need some help setting up a query. There are three tables and the relevant fields are shown below: CHILDREN (id, firstname, lastname)RECORD (id, child_id, startdate, enddate) [enddate will be 0000-00-00 if not yet ended]ITEM (id, record_id, date, desc, hours) Children table might show something like17, Peter, Brown22, Sammy, Watkins Whenever a child is accepted into our service we create a record with the start date and eventually an end date when they leave. The record table will also show their support worker and other info but that's not important right now. Record table might show:6, 22, 2012-02-10, 0000-00-00 [shows Sammy's record starting 10th Feb and still ongoing as no end date]9, 17, 2012-03-25, 0000-00-00 [shows Peter starting on 25th March] Whenever a child undertakes an activity it is entered in the item table.Item table might show1, 9, 2012-03-26, Chess Club, 4.00 [Peter attended chess club for 4 hours]2, 9, 2012-03-28, Theatre Group, 2.00 [Peter - theatre group for 2 hours]3, 9, 2012-04-10, Chess Club, 3.50 [Peter - chess club for 3.5 hours]4, 6, 2012-03-28, Theatre Group, 2.00 [sammy - theatre group for 2 hours]5, 6, 2012-04-05, Music Therapy, 1.50 [sammy - music therapy or 1.5 hours]6, 9, 2012-04-17, Chess Club, 4.00 [Peter - chess club for 4 hours]7, 6, 2012-04-14, Theatre Group, 2.50 [sammy - theatre group for 2.5 hours]8, 6, 2012-04-20, Music Therapy, 1.00 [sammy - music therapy for 1 hour]and so on. Now, I need a list of all activites undertaken by each child with an open record (that is - no end date). My basic query isSELECTCONCAT(`children`.`lastname`, ' ', `children`.`firstname`) AS `name`,`item`.`desc` AS `activity`,`item`.`hours` AS `time`FROM`children`, `record`, `item`WHERE`record`.`child_id`=`children`.`id`AND`item`.`record_id`=`record`.`id`AND`record`.`end`='0000-00-00'ORDER BY `children`.`lastname`, `children`.`firstname` This gives something like:Brown Peter, Chess Club, 4.00Brown Peter, Theatre Group, 2.00Brown Peter, Chess Club, 3.50Brown Peter, Chess Club, 4.00Watkins Sammy, Theatre Group, 2.00Watkins Sammy, Music Therapy, 1.50Watkins Sammy, Theatre Group, 2.50Watkins Sammy, Music Therapy, 1.00 This will do... but I would like to group each child's activities together to count the number of sessions and then the total hours for each activity. So the results will be:Brown Peter, Chess Club, 3, 11.50 [ie he has had three sessions in the chess club totalling 11.5 hours]Brown Peter, Theatre Group, 1, 2.00 [1 session totalling 2 hours etc]Watkins Sammy, Theatre Group, 2, 4.50Watkins Sammy, Music Therapy, 2, 2.50 I'm getting confused with counts, sums and group by statements and would appreciate your help. Ideally I would also like to include some sort of roll up so that I can get Peter showing a total of 4 sessions totalling 13.5 hours and Sammy showing 4 sessions totalling 7 hours but this is not important if it is going to be too difficult.Thanks for your help.

[Err]

Personally, I would either do more queries to narrow it down even more or create a script that processes the information outputed which then puts it into tabular data the way you want.

[murfitUK]

After much hair-pulling and gritting of teeth I have finally solved my problem. I realised I wasn't counting sessions properly. I had COUNT(`item`.`desc`) instead of COUNT(*) which was giving incorrect results. The query I've produced is: SELECTCONCAT(`children`.`lastname`, ', ', `children`.`firstname`) AS `Name`,IFNULL(`item`.`desc`, 'Total--->') AS `Activity`,COUNT(*) AS `Sessions`,SUM(`item`.`hours`) AS `Total`FROM`record`, `children`, `item`WHERE`record`.`child_id`=`children`.`id`AND`item`.`record_id`=`record`.`id`AND`record`.`end`='0000-00-00'GROUP BY`Name`,`item`.`desc`WITH ROLLUP This give me the results I expected. However I've just realised that if two children have the exact same name then it will group them both together - the totals will still be correct but it won't differentiate between the two. I will have to include the child id and then use that to group instead of name but that will throw up its own problem because we can't use order by with groupings. So I think it would be easier to ensure we never have two children with the same name!