Conditonal SUM function, or similar conditional aggregates

Are there any conditional aggregate functions, such as SUM()?

An example would probably be the best way to describe what I'm
trying to do...

I have the following table, named Orders, with the following records:

ItemNo qty_ord paid
-- ---- --
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
Z200L 1 50.00
Z200L 2 100.00

I want to produce the following result set:

ItemNo qty_gross qty_net
-- ---- ---
T101B 11 7
Z200L 3 3

The "qty_gross" column in the result set is the sum of
total items ordered within the ItemNo grouping.
Easy enough. However, I also want a column "qty_net" that
is the sum of qty_ord but ONLY IF the amount in the
"paid" column is > 0.

I tried using the HAVING clause, but that produces a
catch 22 situation. If I say "HAVING paid > 0" then
the qty_gross column is wrong because it leaves out rows
that contain records with paid = 0 values. If I leave
out the HAVING clause, then the "qty_net" is wrong.

Any ideas?

select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_net
from Orders
group by qty_ord, paid, ItemNo
having paid > 0 ???

Thanks,
RobbieOn 15 Feb 2005 06:17:24 -0800, RobbieGotNeeds@.netscape.net wrote:

>Are there any conditional aggregate functions, such as SUM()?
(snip)

Hi Robbie,

No. But you can use any expression in an aggregate function, including the
conditional CASE expression.

>An example would probably be the best way to describe what I'm
>trying to do...
(snip)
>I have the following table, named Orders, with the following records:
>ItemNo qty_ord paid
>-- ---- --
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>Z200L 1 50.00
>Z200L 2 100.00
>
>I want to produce the following result set:
>ItemNo qty_gross qty_net
>-- ---- ---
>T101B 11 7
>Z200L 3 3

SELECT ItemNo,
SUM(qty_ord) AS qty_gross,
SUM(CASE WHEN paid > 0 THEN qty_ord ELSE 0 END) AS qty_net
FROM Orders
GROUP BY ItemNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

conditional aggregate?

Is there any way to add some sort of condition, or where clause to an
aggregate function? I have a report with three nested lists - the detail is
in the inner list, list3. In the second (middle) list, I want to count the
number of rows for column x where column y is not equal (<>) "This".
I have tried adding a textbox in the 3rd list to tally rows where the
condition satisfies a count or a 'tic', and then attempted to add another
expression in list 2 that would sum the new textbox values in list 3, but am
ripping my hair out with error messages - what is the one about 'aggregates
can only be used on report items in the header and footer'? As far as I
understand, you can't really use Fields in the header or footer anyway - so
how one would use an aggregate function in that manner is beyond me...
Anyway, I know Crystal offers a variety of ways to accomplish this type of
thing - is there anyway to do a sum or a count for field X in a detail group
where field Y in the same detail group meets a certain criteria?
Thanks,Myles:
You can do a count in a group based on the condition or expression.
You can do soemthing like this:
=RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
Or you can have this expression for you count:
CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
Hope this answers your question. For more information, search for
RunningValue in reporting services on msdn.
"Myles" wrote:
> Is there any way to add some sort of condition, or where clause to an
> aggregate function? I have a report with three nested lists - the detail is
> in the inner list, list3. In the second (middle) list, I want to count the
> number of rows for column x where column y is not equal (<>) "This".
> I have tried adding a textbox in the 3rd list to tally rows where the
> condition satisfies a count or a 'tic', and then attempted to add another
> expression in list 2 that would sum the new textbox values in list 3, but am
> ripping my hair out with error messages - what is the one about 'aggregates
> can only be used on report items in the header and footer'? As far as I
> understand, you can't really use Fields in the header or footer anyway - so
> how one would use an aggregate function in that manner is beyond me...
> Anyway, I know Crystal offers a variety of ways to accomplish this type of
> thing - is there anyway to do a sum or a count for field X in a detail group
> where field Y in the same detail group meets a certain criteria?
>
> Thanks,|||thank you sam, I will give that a try!
"sam" wrote:
> Myles:
> You can do a count in a group based on the condition or expression.
> You can do soemthing like this:
> =RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
> Or you can have this expression for you count:
> CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
> Hope this answers your question. For more information, search for
> RunningValue in reporting services on msdn.
> "Myles" wrote:
> > Is there any way to add some sort of condition, or where clause to an
> > aggregate function? I have a report with three nested lists - the detail is
> > in the inner list, list3. In the second (middle) list, I want to count the
> > number of rows for column x where column y is not equal (<>) "This".
> >
> > I have tried adding a textbox in the 3rd list to tally rows where the
> > condition satisfies a count or a 'tic', and then attempted to add another
> > expression in list 2 that would sum the new textbox values in list 3, but am
> > ripping my hair out with error messages - what is the one about 'aggregates
> > can only be used on report items in the header and footer'? As far as I
> > understand, you can't really use Fields in the header or footer anyway - so
> > how one would use an aggregate function in that manner is beyond me...
> >
> > Anyway, I know Crystal offers a variety of ways to accomplish this type of
> > thing - is there anyway to do a sum or a count for field X in a detail group
> > where field Y in the same detail group meets a certain criteria?
> >
> >
> > Thanks,