First of all, I'm not a database designer but I have sufficient knowledge to be dangerous with them
Second, not sure if it belongs here or in software. If in the wrong forum, can a moderator please move it.
I'm using the following query to retrieve some data from multiple tables. It lists the affected services per rfs.
Code:
select distinct rfs.PK_rfs,rfs.status,rfs.datim_open,
rfs.datim_resolved,unix_timestamp(rfs.datim_resolved)-unix_timestamp(rfs.datim_open) as lapse_resolved,
rfs.datim_closed,unix_timestamp(rfs.datim_closed)-unix_timestamp(rfs.datim_open) as lapse_closed,
services.service
from rfs
left join rfsaffected on rfs.PK_rfs=rfsaffected.FK_rfs
left join group_srvc on group_srvc.PK_grpsrvc=rfsaffected.grpsrvc
left join services on services.PK_srvc=group_srvc.FK_srvc
where rfsaffected.active='y' AND datim_open>='2006-08-02 00:00:00' AND datim_open<='2006-09-05 08:53:34'
order by rfs.PK_rfs,service,datim_open
I think that the database structure can be derived from this query; if not, I can try to draw or explain it.
The main tables are 'rfs' and 'rfsaffected' (one-to-many relationship). The other tables are basically help tables.
This query contains two calculated colums. I like to sum the values of one of those columns grouped by service. The result only has to contain the service and the total lapse_resolved or total lapse_closed.
Is this possible by modifying the given query? My attempts till now have failed (either mysql is not happy with the syntax or I get incorrect results). I think that the problem is that I don't know how to combine the aggregate function with the distinct keyword.
For now, I've figured out how to use a temporary table and run the aggregate query on that table. Unfortunately I have to give the enduser permission to create tables, something that makes me feel uncomfortable.
The MySQL version is 4.0.23a and all tables are MyISAM (just in case the latter is relevant).
