Adding the grouped feilds

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Adding the grouped feilds

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I think just adding a SUM() around the TIMEDIFF should do it. You might have to cast the timediff to seconds though.
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post 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.
AlbinoJellyfish
Forum Commoner
Posts: 76
Joined: Sun Apr 04, 2004 7:39 pm

Post by AlbinoJellyfish »

Thanks, the sum did the trick!
Post Reply