MySQL optimization - prevent subqueries

Is it possible for me to change this query so I can do开发者_JAVA百科 like COUNT(WHERE type_id = 2) AS success, COUNT(WHERE type_id = 1) AS error to prevent using subqueries?

SELECT
                (SELECT COUNT(id) FROM log AS success_log WHERE type_id = 2 AND site_id = site.id AND DAYOFYEAR(success_log.created)) AS success,
                (SELECT COUNT(id) FROM log AS success_log WHERE type_id = 1 AND site_id = site.id AND DAYOFYEAR(success_log.created)) AS error,
                (SELECT COUNT(id) FROM log AS success_log WHERE site_id = site.id AND DAYOFYEAR(success_log.created)) AS total,
                DATE_FORMAT(log.created, "%m-%d-%y") AS `day`
            FROM log
            WHERE site_id = ?
            GROUP BY DAYOFYEAR(created)`

You can use single count with GroupBy TypeId and insert them in the temp table and then select the way you want it.

since they were all querying from the log table for the same site, you can just run through the table ONCE and just apply and IMMEDIATE IF() test inside a sum.... If the record is of a given type, count as 1, otherwise 0... Gets a sum of each of type 1 or type 2 and simple count(*) gets the overall count for the day in question.

SELECT
      DATE_FORMAT(log.created, "%m-%d-%y") AS `day`,
      sum( if( type_id = 2, 1, 0 )) as NumberSuccess,
      sum( if( type_id = 1, 1, 0 )) as NumberError,
      count(*) as TotalRecords
   from
      log
   where
      site_id = ?
   group by
      DAYOFYEAR(created)