Page 1 of 1

Adding the grouped feilds

Posted: Mon Jul 25, 2005 2:03 pm
by AlbinoJellyfish
Ok, so I have a query:

Code: Select all

select
TIMEDIFF(endTime,startTime) AS 'usage',
ediss7.opc,
ediss7.dpc,
ediss7.cic,
editrunkgroup.trunkGroup,
editrunkgroup.pointCode2,
editrunkgroup.pointCode1,
ediss7.startTime,
ediss7.endTime from ediss7 left join editrunkgroup
 on  ((ediss7.cic >= editrunkgroup.ciclow and
ediss7.cic <=editrunkgroup.cichigh)and
((ediss7.opc = editrunkgroup.pointcode1 or ediss7.opc
= editrunkgroup.pointcode2)
and  (ediss7.dpc = editrunkgroup.pointcode1 or
ediss7.dpc = editrunkgroup.pointcode2)))
Where
(editrunkgroup.trunkGroup is null) Group by ediss7.cic, ediss7.dpc, ediss7.opc
It selects everything that doesn't fall into a predefined category. I want it to take the duplicate entries (cic, dpc, and opc being the same) and add the usage time of the grouped entries and have it display a total for each group. How would I do this?

Posted: Mon Jul 25, 2005 2:14 pm
by pickle
Making your query prettier (at least in my eyes):

Code: Select all

SELECT
   TIMEDIFF(endTime,startTime) AS 'usage',
   ediss7.opc,
   ediss7.dpc,
   ediss7.cic,
   editrunkgroup.trunkGroup,
   editrunkgroup.pointCode2,
   editrunkgroup.pointCode1,
   ediss7.startTime,
   ediss7.endTime 
FROM 
   ediss7 LEFT JOIN editrunkgroup
       on  ((ediss7.cic >= editrunkgroup.ciclow and 
             ediss7.cic <=editrunkgroup.cichigh) and
            ((ediss7.opc = editrunkgroup.pointcode1 or 
              ediss7.opc = editrunkgroup.pointcode2) and
             (ediss7.dpc = editrunkgroup.pointcode1 or
              ediss7.dpc = editrunkgroup.pointcode2)))
WHERE
   (editrunkgroup.trunkGroup is null)
GROUP BY
   ediss7.cic, 
   ediss7.dpc, 
   ediss7.opc


Could you explain what you're trying to do? I couldn't quite figure it out.

Posted: Mon Jul 25, 2005 2:17 pm
by nielsene
I think just adding a SUM() around the TIMEDIFF should do it. You might have to cast the timediff to seconds though.

Posted: Mon Jul 25, 2005 2:24 pm
by AlbinoJellyfish
This query goes into a database, selects some entries that dont fall into a "trunkgroup" that the on statement checks. I want to group these entries that dont fall into a trunkgroup together if they have the same CIC, DPC and OPC. These entries are called "ghost trunks" because the trunkgroup doesnt exist. But I also need to add the "usage" time of the entries in the ghost trunk together to form a total usage time for each "ghost trunk". This prevents duplicates in a list but also shows the correct time each of those groups had. Right now it groups them together, but doesn't add up the times of each in the group so it is way under what it should be.

Posted: Mon Jul 25, 2005 2:45 pm
by AlbinoJellyfish
Thanks, the sum did the trick!