Problem with a complex query

[FreezeS]

Hi guys,

I have the following tables:

calldata (callID, cost, duration, carrierID, prefix) - callID is the pk

unsuccess(callID, prefix, carrierID) - callID is completely unrelated to the one in calldata

zones (prefix, name) - prefix is the link with the other tables

carriers (id, name)


Now, what I want is something like this:

prefix, total costs for this prefix, total duration, calls for carrier 1, failed calls for carrier 1, percent of successful calls for carrier 1, calls for carrier 2, failed calls for carrier 2, percent of successful calls for carrier 2, ... and the same for all other carriers


The query, as I've figured it out until now should be something like this:

select c.prefix, sum(c.cost) as cost, sum(c.duration) as TotalDuration,

@s:=(select count(*) from calltable where carrierID=1) as success1, @u:=(select count(*) from unsuccess where prefix=c.prefix and carrierID=1) as unsuccess1, @u/(@s+@u) as ASR1,

....

from calldata c
inner join zones z on z.prefix=c.prefix
inner join carriers ca on ca.id=c.carrierID
group by c.prefix
order by cost desc


There are multiple problems with this approach:

1. It takes FOREVER on 500.000 items in the database. This ammount of items is to be entered daily, so I want something a "little bit" faster

2. The carriers are manually put into the sql query. I need a query that returns the results dinamically, for all the carriers in the table


The examples I gave here are just with the basic fields, because the actual databases are a lot bigger (~60 fields).

Any help will be apreciated.
Thank you.

 

[ericbrunson]

create a union of calltable and unsuccessful giving a static indicator of successful or unsuccessful and faking the missing fields, then operate on that as a single table summing the success/unsuccess.

Something like this:

select c.prefix, 
       sum(c.cost) as cost, 
       sum(c.duration) as TotalDuration, 
       sum( success ),
       sum( unsuccess ),
       sum( unsuccess ) / ( count(*) )
from    
  ( select cost, duration, 
           carrierID, prefix, 1 success, 0 unsuccess
    from   calldata
    union
    select 0 cost, 0 duration,
           carrierID, prefix, 0 success, 1 unsuccess
    from   unsuccessful ) c
inner join zones z on z.prefix = c.prefix
inner join carriers ca on ca.id = c.carrierID
group by c.prefix
order by cost desc

That's untested, so you may have to fix some syntax. Time that and then start looking at indexing.