Mysql group by help

Question

Hello, after several attempts, I can not solve this problem. I have the following mysql query:

SELECT
                  date_format(connect_time,"%Y-%m-%d %H") AS date,
                  NotConnected,
                  count(id) as calls,
                  sum(`duration`)/60 as minutes
   FROM test.`CDR_Vendors`,

   (SELECT        
       date_format(connect_time,"%Y-%m-%d %H") AS date,
       Count(id) as NotConnected
       FROM `CDR_Vendors_Failed`
       inner join Vendors on (CDR_Vendors_Failed.i_vendor = Vendors.i_vendor)
       inner join Customers on (CDR_Vendors_Failed.i_customer = Customers.i_customer)
       WHERE
       Customers.name like "W%"
       and
       Vendors.name like "D%"
       and connect_time between curdate() and now()
       GROUP by date
       ORDER BY date

   )Failed
       inner join Vendors on (CDR_Vendors.i_vendor = Vendors.i_vendor)
       inner join Customers on (CDR_Vendors.i_customer = Customers.i_customer)
       WHERE
       Customers.name like "W%"
       and
       Vendors.name like "D%"
       and connect_time between curdate() and now()
       GROUP by date
       ORDER BY date

The result:

date,Notconnected,calls, minutes
'2010-10-25 00', 408, 900, 6611.00
'2010-10-25 01', 408, 456, 2777.60
'2010-10-25 02', 408, 204, 1545.80
'2010-10-25 03', 408, 108, 1951.80
'2010-10-25 04', 408, 192, 895.60
'2010-10-25 05', 408, 300, 544.20
'2010-10-25 06', 408, 540, 961.20
'2010-10-25 07', 408, 1728, 5027.60
'2010-10-25 08', 408, 4968, 20986.40
'2010-10-25 09', 408, 7884, 33065.00
'2010-10-25 10', 408, 7836, 28182.20
'2010-10-25 11', 408, 1800, 3587.80

I noticed that the first value of Notconnected field is repeated.

how can I fix this please ?

Answer 1

pls check the database tables and value for not connected fields.

Answer 2

This query will be very slow. In which table do you have connect_time field? If in CDR_Vendoes and CRD_Vendors_Failed, then you don't need subquery.

Maybe you need join with CDR_Vendors_Failed table?

SELECT
                  date_format(connect_time,"%Y-%m-%d %H") AS date,
                  Failed.NotConnected,
                  count(id) as calls,
                  sum(`duration`)/60 as minutes
   FROM test.`CDR_Vendors`
       inner join Vendors on (CDR_Vendors.i_vendor = Vendors.i_vendor)
       inner join Customers on (CDR_Vendors.i_customer = Customers.i_customer)
       inner join
          (SELECT        
               date_format(connect_time,"%Y-%m-%d %H") AS date,
               Count(id) as NotConnected
               FROM `CDR_Vendors_Failed`
               inner join Vendors on (CDR_Vendors_Failed.i_vendor = Vendors.i_vendor)
               inner join Customers on (CDR_Vendors_Failed.i_customer = Customers.i_customer)
               WHERE
               Customers.name like "W%"
               and
               Vendors.name like "D%"
               and connect_time between curdate() and now()
               GROUP by date
               ORDER BY date
            )Failed on Failed.date = date_format(connect_time,"%Y-%m-%d %H")
       WHERE
       Customers.name like "W%"
       and
       Vendors.name like "D%"
       and connect_time between curdate() and now()
       GROUP by date
       ORDER BY date