aggregate function in subquery

Question

aggregate function in subquery

Pro100

Right there with Babe

Points: 769
More actions
July 13, 2016 at 2:04 am

#309040

I'm facing some problem with a SQL query. Probably solution is pretty easy but I'm new to SQL, here is part which gave me an error :
isnull(sum(case
when[materialGroup] in (Select grupa
from [programDoFaktur].[dbo].[grupyTowarowe]
where typ like '%papier%')
then isnull(cast([binQuantity] as int), 0)
end), 0) as papier
Error is :
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
But when I do it like this :
isnull(sum(case
when[materialGroup] in ('test')
then isnull(cast([binQuantity] as int), 0)
end), 0) as papier
Then it works correctly but I had to do every change which user want in my code when I could just let them insert what they want into the database.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The answer is in the error message. You can't use an aggregate function ([font="Courier New"]SUM[/font]) on an expression containing a subquery ([font="Courier New"]SELECT grupa FROM grupyTowarowe WHERE typ LIKE '%papier%'[/font]).

John

Pro100 Right there with Babe Points: 769 More actions · Answer 2

Yea, i know what SQL tells me but I post it here cause i thought someone will help me rewrite it.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Not quite sure what your requirement is here, but you could try joining your table to grupyTowarowe on materialGroup = grupa.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Pro100 (7/13/2016)
Yea, i know what SQL tells me but I post it here cause i thought someone will help me rewrite it.

Post the whole query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 76944 More actions · Answer 5

You can place your CASE expression in a CTE, derived table, or CROSS/OUTER APPLY and then do the SUM in the main query.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA