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.