The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

As the error says, some of the data types aren't comparable. In your statement I'd guess it's P.ProductImage.

One option is that you first select the 'normal' data and then use that result set and combine it with the image data. In other words, something like

select a.*,
       (select p.productimage 
        from product p
        where p.productsno = a.productsno) AS ProductImage
from (
      select P.ProductSNo,
      P.ProductName,
      P.ProductDescription,
      P.ProductPrice,
      P.CategorySNo,
      P.CategorySNo,
      P.ProductQuantity,
      C.CategoryName,
      Isnull(Sum(CP.TotalProducts),0) as ProductSold
      From Products P
      Inner Join Category C
      On P.CategorySNo=C.CategorySNo
      LEFT Join CustomerProducts CP
      On P.ProductSNo=CP.ProductSNo
      Group BY
      P.ProductSNo,
      P.ProductName,
      P.ProductDescription,
      P.ProductPrice,
      P.CategorySNo,
      P.ProductQuantity,
      C.CategoryName) a

The joining column may be incorrect in the example, but you know the structure of the database better :)