CODEWITHSUNDEEP
sqlsql-servert-sql
Gievkeks
Gievkeks

Reputation: 13

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

Upvotes: 1

Views: 121

Answers (4)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4263

I reworked you query slightly by separating the subqueries.

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

Upvotes: 0

Charlieface
Charlieface

Reputation: 72501

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.

Upvotes: 3

Sergey
Sergey

Reputation: 5250

I guess you can try to rework your query as

 SELECT X.ID,X.beginTime,X.endTime,X.name,CR.CNTT,CR.TOTAL_VOLUME
 FROM
 (
     Select DISTINCT Contracts.id, Contracts.beginTime, Contracts.endTime,      Suppliers.name
     FROM Contracts 
     LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract
 )X
 CROSS APPLY
 (
   SELECT COUNT(p.id)AS CNTT,SUM(p.volume) AS TOTAL_VOLUME
   from production as p where p.id_contract = X.id
 )CR

Upvotes: 0

Hardik Shah
Hardik Shah

Reputation: 161

You should use it like below:

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

Upvotes: 0

Related Questions