Divide COUNT by SUM ? 1

[katgirl]

Hi,

Need help writing this query...!

For each calendar year, select all customers who cancelled (as given by a value in the "cancel date" field, otherwise null).

So, for each year, do a count of the "cancel date" (i.e. where "cancel date" is not null). Then I need to divide the yearly cancellation count by the total customer base (as given by the SUM of all customers in the table).

So, it should look something like (but not this):

Select DATEPART(yyyy,canceldate) As 'CancelYear',
count(*) / SUM(SELECT count(*) FROM customers)
from customers
where statusid <= 20
Group by DATEPART(yyyy,canceldate)
order by DATEPART(yyyy,canceldate)

Thanks

 

[bborissov]

Just remove SUM():

Select DATEPART(yyyy,canceldate) As 'CancelYear',
       count(*) / (SELECT count(*) FROM customers)
from customers
where statusid <= 20
Group by DATEPART(yyyy,canceldate)
order by DATEPART(yyyy,canceldate)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[katgirl]

Duh.

Ok, I ran it without the sum, here's what i get:

CancelYear
----------- -----------
NULL 0
1998 0
1999 0
2000 0
2001 0
2002 0
2003 0
2004 0
2005 0
2006 0

which is not right

hmmmmmm

 

[bborissov]

Select DATEPART(yyyy,canceldate) As 'CancelYear',
count(*)*1.0 / (SELECT count(*) FROM customers)
from customers
where statusid <= 20
Group by DATEPART(yyyy,canceldate)
order by DATEPART(yyyy,canceldate)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[katgirl]

Thanks. I wonder why you need to multipy by 1?

 

[katgirl]

Another star for this answer



Is there a way to divide the first count(*) by the second "count(*) of customers" where the customer "saledate" (another column) is in the group by year??

That is, I want to divide the count(*) of cancelled customers by year by the number of customers who signed up that year, i.e. "saledate".

So, it's not as simple as just counting ALL customers in the entire customers table. The % is only meaningful if it's divided by the number of customers who became customers in the particular year.

THANKS