CODEWITHSUNDEEP
sqlsql-server
N. Little
N. Little

Reputation: 69

SQL Server: cannot perform an aggregate function on an expression containing an aggregate or a subquery

I have these two tables:

Storage:

id (int, PK) 
articleNr (nvarchar(10))
shelf (nvarchar(10))
number (int)

Orders:

orderNr (int, PK)
articleNr (nvarchar(10))
ordered (int)
picked (int)

I want to know how many items of a certain article number are available for purchase.

So the total number of articles is just

select sum(number) 
from storage 
group by articleNr

And the number of ordered and not yet picked articles is just

select sum(ordered) - sum(picked) 
from orders 
group by articleNr

So all I want to do really is to just subtract the latter from the former and I have my answer, but when I try to do that in a query I can't do that because it's a SUM within a SUM. What I really want to do is something like: 

select sum(query1) - sum(query2)

(Where query1 is the first query above and query2 the second)

But when I do that I get the error message that's in the subject.

Upvotes: 0

Views: 1859

Answers (1)

Arvo
Arvo

Reputation: 10570

Maybe this:

select coalesce(s.articleNr, o.articleNr) as articleNr, 
    coalesce(s.q_stored,0) - coalesce(o.q_topick,0) as q_remaining
from (
    select articleNr, sum(number) as q_stored
    from storage 
    group by articleNr) s
full join (
    select articleNr, sum(ordered) - sum(picked)  as q_topick
    from orders 
    group by articleNr
) o on o.articleNr = s.articleNr

Upvotes: 1

Related Questions