"zoran***@hotmail.com" wrote:

> create table calls
> (
> customer char(10),
> call_date datetime,
> called_number char(10),
> call_ammount char(10)
> )
>
> insert calls values('Customer1','1/1/2005','555-1234','1.00')
> insert calls values('Customer2','1/2/2005','555-1231','0.50')
> insert calls values('Customer3','1/1/2005','555-1232','5.00')
> insert calls values('Customer4','2/1/2005','555-1233','1.00')
> insert calls values('Customer1','2/2/2005','555-1234','1.00')
> insert calls values('Customer1','2/3/2005','555-1235','1.00')
> insert calls values('Customer2','2/4/2005','555-1236','3.00')
> insert calls values('Customer4','2/1/2005','555-1237','2.00')
> insert calls values('Customer5','3/1/2005','555-1238','2.00')
>
> I need to get a list of customers calling for > $2.50 a month.
>
> select MONTH(call_date) as Months
> , customer
> ,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
> ,count( distinct  customer ) as 'No. of cust.'
> from calls with(nolock)
> where call_date >= '1/1/2005'
> group by MONTH(call_date), customer
> --with rollup
> HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
> order by Months
> , 'SUM Ammount $' desc
>
> (without the counts) will get what I need .
>
> Months    Customer    SUM $    No. of cust.
> 1    Customer3     5.00    1
> 2    Customer2     3.00    1
> 2    Customer4     3.00    1
> 3    Customer5     3.00    1
>
> Turns out , I 'd also need to get monthly counts (ideally with $
> totals) of these customers .
> This is where I get stuck.
> When I add a count to the query I have the result as shown above but
> what I'd need is
>
> Months    SUM $    No. of cust.
> 1    5.00    1
> 2    6.00    2
> 3    3.00    1
>
> or totals like with Rollup but Rollup doesn't work with distinct count.
>
> BTW yes, this is simplified, and I know this is bad (no) design but
> there is nothing I can do about that at the moment.
>
> TIA
>
> Z
>
>