CODEWITHSUNDEEP
sqlsql-servercross-apply
GotYa
GotYa

Reputation: 121

Two aggregates in one cross apply possible?

I'm very much new to SQL and I'm trying to use CROSS APPLY, something I know very little about.

I'm trying to pull two SUMs of items sorted by an ID from two different tables. One SUM of all items dispensed by a cartridge, one SUM of all items refilled into a cartridge. The dispenses and refills are in separate tables. In Sample 1 you can see a piece of code that works for one of these two SUMs, currently its for the Dispensed SUM, but it also works if I change everything for the refilled SUM. Point being I can only do one SUM in this CROSS APPLY, regardless which one of the two.

So it goes wrong when I try to pull both SUMs in this one CROSS APPLY, probably cause I don't really know what I'm doing. I try to do this with the code seen in Sample 2 (which is pretty much the same code).

Some extra context:

There are two ID's here that are important:

The CartridgeRefill.FK_CartridgeRegistration_Id (or ID) is the ID for a cartridge itself. The FK_CartridgeRefill_Id is the ID for a refill, a cartridge can go through multiple refills and dispenses are registered by what refill they were dispensed from. That's why you can see the same ID multiple times in the output.

Sample 1:

SELECT CartridgeRefill.FK_CartridgeRegistration_Id AS ID, Sums.Dispensed
FROM CartridgeRefillItem
CROSS APPLY (
        SELECT SUM(CartridgeDispenseAttempt.Amount) AS Dispensed
        FROM CartridgeDispenseAttempt
        WHERE CartridgeRefillItem.FK_CartridgeRefill_Id = CartridgeDispenseAttempt.FK_CartridgeRefill_Id
) AS Sums
JOIN CartridgeRefill ON CartridgeRefillItem.FK_CartridgeRefill_Id = CartridgeRefill.FK_CartridgeRefill_Id

Sample 2:

SELECT CartridgeRefill.FK_CartridgeRegistration_Id AS ID, Sums.Dispensed, Sums.Refilled
FROM CartridgeRefillItem
CROSS APPLY (
        SELECT SUM(CartridgeDispenseAttempt.Amount) AS Dispensed
            ,SUM(CartridgeRefillItem.Amount) AS Refilled
        FROM CartridgeDispenseAttempt
        WHERE CartridgeRefillItem.FK_CartridgeRefill_Id = CartridgeDispenseAttempt.FK_CartridgeRefill_Id
) AS Sums
JOIN CartridgeRefill ON CartridgeRefillItem.FK_CartridgeRefill_Id = CartridgeRefill.FK_CartridgeRefill_Id

When I run sample 1 I get this output:

ID    Dispensed
10    95
8     143
6     143
11    70
11    312
11    354
8     19
8     24
8     3
8     33

This output is correct, it displays the number of dispensed items next to the ID it belongs to.

This is the error I get when I run sample 2:

Msg 4101, Level 15, State 1, Line 15
Aggregates on the right side of an APPLY cannot reference columns from the left side.

But what I want to see is:

ID    Dispensed     Refilled (example)
10    95            143
8     143           12
6     143           etc...
11    70
11    312
11    354
8     19
8     24
8     3
8     33

I think it has something to do with CROSS APPLY running line by line? But again, I still don't exactly know what I'm doing yet. Any help would be really appreciated and please ask whatever you need to know :)

Upvotes: 1

Views: 654

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You may not want aggregation at all. The number of rows is not being reduced, so this may be what you want:

SELECT cr.FK_CartridgeRegistration_Id AS ID, 
       d.Dispensed, cr.Amount AS Refilled
FROM CartridgeRefillItem cr CROSS APPLY
     (SELECT SUM(cd.Amount) AS Dispensed
      FROM CartridgeDispenseAttempt c
      WHERE cr.FK_CartridgeRefill_Id = cd.FK_CartridgeRefill_Id
     ) d;

I would expect that you want separate totals for each id. If so, then your sample results are not sensible because ids are repeated. But this would seem to do something useful:

select id, sum(refill_amount) as refill_amount,
       sum(dispensed_amount) as dispensed_amount
from ((select cr.FK_CartridgeRegistration_Id as id,
              cr.Amount as refill_amount,
              0 as dispensed_amount
       from CartridgeRefillItem cr
      ) union all
      (select cd.FK_CartridgeRegistration_Id as id,
              0, cd.Amount
       from CartridgeDispenseAttempt cd
      )
     ) c
group by id

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Error is quite self explanatory, you cannot run an aggregate using a reference that's outside of CROSS APPLY. You'll need to rewrite your query by adding a additional subquery to calculate SUM or use a GROUP BY clause. I've quickly scraped this:

SELECT CartridgeRefill.FK_CartridgeRegistration_Id AS ID, Sums.Dispensed, SUM(CartridgeRefillMedication.Amount) AS Refilled
FROM CartridgeRefillItem
CROSS APPLY (
        SELECT SUM(CartridgeDispenseAttempt.Amount) AS Dispensed
        FROM CartridgeDispenseAttempt
        WHERE CartridgeRefillItem.FK_CartridgeRefill_Id = CartridgeDispenseAttempt.FK_CartridgeRefill_Id
) AS Sums
JOIN CartridgeRefill ON CartridgeRefillMedication.FK_CartridgeRefill_Id = CartridgeRefill.FK_CartridgeRefill_Id
GROUP BY CartridgeRefill.FK_CartridgeRegistration_Id;

Hopefully this works.

Upvotes: 2

Related Questions