Returning multiple aggregated columns from 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.

The desired output would be like so:

contract1Id, supplierInfoContract1, nrItemsContract1, sumVolumeContract1
contract2Id, supplierInfoContract2, nrItemsContract2, sumVolumeContract2

Answer

Instead of using DISTINCT and subqueries, use GROUP BY and normal joins to get the aggregates. And always use aliases, it will make your life easier:

SELECT
    c.id,
    c.beginTime,
    c.endTime,
    s.name,
    COUNT(p.id) prod_count,
    SUM(p.volume) prod_vol
FROM Contracts c
LEFT JOIN production p on p.id_contract = c.id
LEFT JOIN Suppliers s on c.id = s.id_contract
GROUP BY c.id, c.beginTime, c.endTime, s.name;

Another option is to APPLY the grouped up subquery:

SELECT DISTINCT
    c.id,
    c.beginTime,
    c.endTime,
    s.name,
    p.prod_count,
    p.prod_vol
FROM Contracts c
LEFT JOIN Suppliers s on c.id = s.id_contract
OUTER APPLY (
    SELECT
        COUNT(p.id) prod_count,
        SUM(p.volume) prod_vol
    FROM production p WHERE p.id_contract = c.id
    GROUP BY ()
) p;

You can also use CROSS APPLY and leave out the GROUP BY (), this uses a scalar aggregate and returns 0 instead of null for no rows.

One last point: DISTINCT in a joined query is a bit of a code smell, it usually indicates the query writer wasn’t thinking too hard about what the joined tables returned, and just wanted to get rid of duplicate rows.