Search the Community

Hi, I want to pivot a table and at this moment i use: SELECT e.guid AS id, e.guid AS guid, TYPE , subtype, md.owner_guid AS owner_guid, site_guid, container_guid, md.access_id AS access_id, username, name, e.time_created, time_updated, name, e.enabled, banned, e.last_action, last_login, MAX( IF( msn.string = 'custom_profile_type', msv.string, NULL ) ) AS custom_profile_type, MAX( IF( msn.string = 'field1', msv.string, NULL ) ) AS field1, MAX( IF( msn.string = 'field1', msv.string, NULL ) ) AS field1 FROM exp_metadata md JOIN exp_metastrings msn ON md.name_id = msn.id JOIN exp_metastrings msv ON md.value_id = msv.id JOIN exp_users_entity u ON u.guid = md.entity_guid JOIN exp_entities e ON e.guid = md.entity_guid GROUP BY guid You can see that I need the field 'field1' as a column with the value attached to it. The problem is that there are multiple fields called 'field1' basicly the options of a multiselect that my users can choose. Obviously using MAX I get the maximum value, ex. when they selected answer1, answer2 and answer3, this query will only fetch answer3. I want to know if there is a way that instead of just 'answer3' I can have the value to be: answer1,answer2,answer3 I'm sorry if it is not really clear. Thanks in advance.

Hi I'm looking for a little direction trying to calculate averages with a pivot table type query I run a query like this: SELECT IFNULL(Worker, 'Totals') AS Operator, `Week 1` FROM ( SELECT w.worker_name as 'Worker', ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1' FROM production JOIN workers AS w USING (worker_id) WHERE product_id='1321' GROUP BY worker_id ) AS sums And get this... Operator Week1 ABaits 0.00 DHarris 0.00 JAvalas 665.14 KIgner 0.00 MAhe 0.00 AReynolds 196.43 JWhitt 0.00 RAloney 422.97 AStorms 148.40 IGlesias 716.62 Which is only correct if in this time period (Week 1) the user has only 1 record. For example JAvalas Week 1 should read 665.15/4=166.29 (there are 4 records this week for him) But AReynolds (196.43) is actually correct because in this period he only had 1 record Since I'm using SUM everything is actually working correctly for what I'm asking, but I need to introduce a way to AVG() these groupd results. I've tried introuducing COUNT(*) at ROUND(SUM(IF(Week(production_date)='1', production_net/(production_time/60*(COUNT(*)),0)),2) As 'Week 1' to modify the 60 divisor, I've tried using ROUND(AVG(IF(Week(production_date)='1', production_net/(production_time/60),0)),2) As 'Week 1' instead of SUM() but end up with some very odd results. And I've tried to sum(production_net)/sum(production_time) but get an invalid use of group functions. I'm not sure what I can do to get the averages here. Any help or advise is always welcome, Thank you