Hi,
Denodo does not support sub queries in SELECT clause. I think what you can do is create an Inner Join between both the tables on the fields you have used in your where clause and use GROUP BY to get the similar results.
For example :
If your desired sub query in select clause is :
Select id , date, (Select Count (product) from product where id = s.id ) as product_count
from sales s
Then you can write the query using JOIN and GROUPBY as
Select s.id, date, Count (p.product ) as product_count
from sales s
INNER JOIN product p
on p.id = s.id
GROUP BY s.id, date
Hope this helps!