aggregate sql

[joemeshuggah]

im new to the world of sql, so im not sure if this is can even be
accomplished in a single query or if two separate queries are required. i am
trying to put together a query where the end result is based on a condition
of an aggregate...i want the sum of the amount billed (and all other
information requested) to appear based on a condition that involves an
operation on the amount billed.


for example:

select name, account, cycle date, sum(amount billed) from billing
information group by name, account, cycle date where ((1-((select amount
billed from billing information where promo id is not null)/(select amount
billed from billing information where promo id is null)))) <>0.19

ive tried a number of different ways, but continually get error messages.

can you use the sum aggregate and include a condition on the attribute being
summed?

 

[John Spencer]

POSSIBLY what you are attempting to do is the following.

SELECT [name], account, [cycle date], Sum([amount billed])
FROM [billing information]
GROUP BY [name], account, [cycle date]
HAVING 1-
(Sum(IIF([Promo id] is not null,amount,0)) /
Sum(IIF([Promo id] is null,amount,0))) <> .19

If that works, but is not what you are attempting to do, then I suggest you
post your question with a description in words of what you are attempting to
do.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..