SQL: How could I turn this into one query?

UPDATE a
    SET CountOfAA=dt.CountOf
    FROM @MediaResurce  a
        INNER JOIN (SELECT 
                        aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf
                        FROM @MediaResurce                      aa
                            LEFT OUTER JOIN @MediaResurce_Pics  bb 
                            ON aa.sku=bb.sku
                        WHERE somecol = 0
                        GROUP BY aa.Sku
                   ) dt ON a.sku=dt.sku

/////////////

UPDATE a
    SET CountOfBB=dt.CountOf
    FROM @MediaResurce  a
        INNER JOIN (SELECT 
                        aa.Sku,ISNULL(COUNT(开发者_开发技巧bb.sku),0) AS CountOf
                        FROM @MediaResurce                      aa
                            LEFT OUTER JOIN @MediaResurce_Pics  bb 
                            ON aa.sku=bb.sku
                         WHERE somecol = 1
                        GROUP BY aa.Sku
                   ) dt ON a.sku=dt.sku

This should be what you are looking for:

UPDATE a
SET 
  CountOfAA = dt.CountOfAA,
  CountOfBB = dt.CountOfBB
FROM @MediaResurce  a
INNER JOIN (
  SELECT
    SUM(
      case somecol 
        when 0 then 1
        else 0
      end
    ) AS CountOfAA,
    SUM(
      case somecol 
        when 1 then 1
        else 0
      end
    ) AS CountOfBB
    FROM @MediaResurce aa
    LEFT OUTER JOIN @MediaResurce_Pics bb 
      ON aa.sku=bb.sku
) dt ON a.sku=dt.sku

Instead of two separate queries to count each instance, you can SUM "1" or "0" based on the requirements of your "somecol".

Well you can easily do it as

UPDATE a
SET CountOfAA=dt.CountOf,
    CountOfBB=dt2.CountOf
FROM @MediaResurce  a
    INNER JOIN (SELECT 
                    aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf
                    FROM @MediaResurce                      aa
                        LEFT OUTER JOIN @MediaResurce_Pics  bb 
                        ON aa.sku=bb.sku
                    WHERE somecol = 0
                    GROUP BY aa.Sku
               ) dt ON a.sku=dt.sku
    INNER JOIN (SELECT 
                    aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf
                    FROM @MediaResurce                      aa
                        LEFT OUTER JOIN @MediaResurce_Pics  bb 
                        ON aa.sku=bb.sku
                     WHERE somecol = 1
                    GROUP BY aa.Sku
               ) dt2 ON a.sku=dt2.sku

Which is the lazy and inneficient way, another method might be something like this:

SUM(somecol) AS 'count where somecol=1'
count(*) - SUM(somecol) AS 'count where somecol=0'
...
where somecol in (0,1) //only need if somecol can be something other than 0 or 1!