count per all entry divide / total count

[edgarbenilde]

hi someone please help me..

i want to count records found in database as per customer status

ex.

Custstatus          Count                 Percentage  (Count / total)
Active                  5                     0.5%  how could i do this at crystal report
Not active             4                      0.4%  how could i do this at crystal report

Please help

[avrail]

you can use GROUP BY
something like this

SELECT COUNT(Custstatus) FROM <YourTableName> 
GROUP BY Custstatus

[edgarbenilde]

yes i'm done with with... my problem is to get the result of count / total
ex.
how many is active / total count of all entries (active, inactive, etc.)
and how many is inactive / total count of all entries (active, inactive, etc.)
and etc if there other entries specefied depend on the user entry

[avrail]

ok,
i think about something and i wish this can help.

SELECT COUNT(Custstatus),dbo.TotalCount() as TotalRows FROM <TableName>
GROUP By Custstatus

where dbo.TotalCount() is Scalar Valued Function
and here is its Definision

USE [<your Database>]
GO
/****** Object:  UserDefinedFunction [dbo].[TotalCount]    Script Date: 08/24/2009 10:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[TotalCount]()
RETURNS int
 
AS
BEGIN
    DECLARE @Return int
    SET @Return = (SELECT COUNT(*) FROM dbo.<yourTableName>)
    return @Return
END

[edgarbenilde]

i found help codes in crystal report formula fields
like this

If {Customer.Region} In ["WA","ID","OR"] Then
    "Northwest"
Else If {Customer.Region} In ["CA","TX","NV","AZ","NM"] Then
    "Southwest"
Else If {Customer.Region} In ["AL","AR","LA","MS","TN","VA","WV","FL","NC","SC","GA"] Then
    "Southeast"
Else If {Customer.Region} In ["CT","DC","DE","MD","NJ","ME","NH","NY","MA","PA","RI","VT"] Then
    "Northeast"
Else If {Customer.Region} In ["UT","CO","WY","NE","MT"] Then
    "Rocky Mountain"
Else
    "Midwest"

in my case i want to count all entries which mymonth field value is 1.
can someone know to do it in crystal report formula fields?

please help

[avrail]

why you insist to do this in your crystal report,
you can do it inside your database

i don't know crystal report well,

[edgarbenilde]

thanks avrail as posted above

Custstatus          Count                 Percentage  (Count / total)
Active                  5                     0.5%  how could i do this at crystal report
Not active             4                      0.4%  how could i do this at crystal report

i want to this format

SELECT COUNT(Custstatus) FROM <YourTableName> GROUP BY Custstatus

based on your reply in post # 2 this is possible but lock of one row Value not found.

this is how to get the result but i don't know how..

count(cuscode) as mycountresult
count (*) as myallentries
mycountresult / myallentries
Group by Custstatus

thats it's but how to make a code for that...
it can be in Access querry or Formula field fo crystal report depends on what is easy to do..

please help me here...

[avrail]

what is your Database Engine ?

[edgarbenilde]

access database 2003

[avrail]

amm,
i didn't use access i use SQL Server
try to use your Query Designer to create 2 views
the first will be your one that you said you can done.

SELECT COUNT(Custstatus) FROM <YourTableName> GROUP BY Custstatus

and the other is for

SELECT COUNT(Custstatus) FROM <YourTableName>

with out any Group

and inside your crystal Report i believe that you can use the two Views.

and then you can do
CountActive/CountRows

[brucevde]

This is very easy to do in Crystal. Assume you have a sample report that is based on the Customer table in the Northwind database. To get a Count and Percentage of Customers by Country

1) Create a Group using the Country field.
2) Insert the CompanyName field in the Details section
3) Right click the CompanyName field and select Insert Summary
4) Select Count from the dropdown list.
5) Check the options "Insert Summary fields for all Groups" and "Insert Grand Total fields"
Two new fields should be created. One in the Group footer the other in the Report Footer
6) Copy and paste each summary field into the same section.
7) Right click on each pasted summary field and select Change Summary
8) Set the option "Show as a Percentage of"
9) Format the report as desired.


You can accomplish the same thing using formulas.

CountByCountry = Count ({Customers.CompanyName},{Customers.Country} )
PercentByCountry = PercentOfCount ({Customers.CompanyName},{Customers.Country} )
TotalCount = Count ({Customers.CompanyName})

[edgarbenilde]

this is helpfull brucevde, but i don't Grand totals (92 and 100&#37 summary.
i only have the bycountry with the count and percentage equevalent...

another issue i found i want to count how many of those records are belong to month of January, Febuary, March, April, May ........ up to December, in my database i have field called mymonth.. how could i do it?
this result would be look like this

                     January         February          March           April          May ...........
Total Records      8                 6                     5                8             10...........

the mymonth field data type is number so data inside it is (1 to 12 as equevalent to jan - dec.)

[edgarbenilde]

this is helpful it's but i didn't get a grand total of 92 and percentage 100%

another things how can i count how many of this records are belong to each month
in my database i have field called myMonth
this is going to be in this format

              January         February           March        April        May ........... upto  Dec
Total           8                   11                 7               9          11                                     92

the mymonth field data type is number so data inside it is (1 to 12 as equevalent to jan - dec.)

this code is the nearest code the result i want.. but this is general i want to put a filter on this, how could i put here if mymonth=1 then Count ({Customers.CompanyName}) and this will be my result for January, then count again mymonth=2 for february..
please help me here...

TotalCount = Count ({Customers.CompanyName})

[edgarbenilde]

somebody please help in post # 13...

[edgarbenilde]

what wrong with this code?
i put this code under AveCountOverWorkdays

{byCustStatus} / {SUMworkDAYS}

in formula field it gives me error "the field is not known"
but that is the name of the field that i have in the formula field..

anybody help me how to divide under formula field...
the denaminator and divisor are both from formula