Count by month in summarized data

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
ZTry using your query as a derived table.
select
months,
sum([SUM $]) as c1,
sum([No. of cust.]) as c2
from
(
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
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
)
group by
months
AMB
"zoranlee@.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
>