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)