How could i optimize this query?

Question

UPDATE a
SET CountOfAA=dt.CountOf,
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 = 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

Answer 1

What's provided is somewhat abstracted, makes it hard to provide feedback.

UPDATE a
   SET CountOfAA = CASE WHEN b.somecol = 0 THEN b.CountOf END,
       CountOfBB = CASE WHEN b.somecol = 1 THEN b.CountOf END
  FROM @MediaResurce a
  JOIN (SELECT aa.Sku,
               somecol,
               ISNULL(COUNT(bb.sku), 0) AS CountOf
          FROM @MediaResurce aa
     LEFT JOIN @MediaResurce_Pics bb ON aa.sku = bb.sku
         WHERE somecol IN (0, 1)
      GROUP BY aa.Sku, somecol) b ON b.sku = a.sku

Answer 2

Just started writing from scratch, turned out similar to OMG's answer:

UPDATE a
SET CountOfAA=dt.CountOfAA,
    CountOfBB=dt.CountOfBB
FROM @MediaResurce a
INNER JOIN (
    SELECT
        aa.sku,
        SUM(CASE WHEN aa.somecol = 0 THEN 1 ELSE 0 END) AS CountOfAA,
        SUM(CASE WHEN aa.somecol = 1 THEN 1 ELSE 0 END) AS CountOfBB
    FROM @MediaResurce_Pics bb 
    INNER JOIN @MediaResurce aa ON aa.sku = bb.sku
    GROUP BY aa.sku
) dt ON dt.sku = a.sku

Points of attention:

• I assumed that 'somecol' is in @MediaResurce. If it exists in @MediaResurce_Pics, I see no reason for the second INNER JOIN
• For bigger tables, a significant speed boost to such a query can be made by setting appropriate indexes on the source tables, such as on 'sku' and 'somecol'. But table variables don't support indexes. Can you change them into temporary or fixed tables? How big is this table?