Returning multiple aggregated columns from MSSQL Subquery

I am trying to extend an existing query by aggregating some rows from another table. It works when I only return one column like this:

Select DISTINCT Contracts.id, Contracts.beginTime, Contracts.endTime, Suppliers.name
(SELECT COUNT(p.id) from production as p where p.id_contract = Contracts.id)
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract

Then I tried to add another column for the aggregated volume:

Select DISTINCT Contracts.id, Contracts.beginTime, Contracts.endTime, Suppliers.name
(SELECT COUNT(p.id), SUM(p.volume) from production as p where p.id_contract = Contracts.id)
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract

However, this returns the following error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I experimented a bit with the EXISTS keyword, but couldn't figure out how to make it work. Also I'm not sure whether this is the way to go in my case.



Read more here: https://stackoverflow.com/questions/66305600/returning-multiple-aggregated-columns-from-mssql-subquery

Content Attribution

This content was originally published by Gievkeks at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: