Count of two fields with reference to someother field

Hi,

I have three fields...field1,field2 and field3.
Data fed into field1 and field2 is numeric and
data fed into field3 is string.

I need to count the number of times each of fieild1 and field2 is fed when field3 is equal to "BA Test". Likewise, I have couple of values defined for field3...I want sum of count of field1 and field2.

In other words, I can call it as conditional counting.

Thanks in advance.

Cheers
Sreenath NookalaIn SQL try:

Select field3, count(field1) field1, count(field2) field2
from
{table}
group by field3

You can then use a function to sum count of field1 and field2|||Actually, I am pulling data from Rational ClearQuest and creating a report in Crystal Reports. MS Access is one of the databases supported by Rational ClearQuest.

I am not sure how SQL commands work in MS Access.

Can we tell me how to do in GUI mode in Crystal Reports.

Thanks
Sreenath Nookala|||In Access write a query and save it. When designing the report, design it based on that query|||well u can use a variable to store count value .. means when ever u get condition true then increment this variable with one ... in this u can get count of flds ...
e.g

numbervar countFld1;
if fld3 = 'BA Test' then
countFld1 = countFld1 + 1

then u can display this variable ... u will have to reset ur variable if want to recalculate against each group ... by using

numbervar countFld1 := 0;

this may solve ur poblem upto the query i understand ...sql

Count how many times a character appeared in a string

Hi all,
I'm having trouble googling this problem ...
Would anyone know the easiest way to obtain the number of times a
character appeared in a given string?
Thanks
ACA WHILE loop will do the trick. Just for fun though, here's a set-based
approach that uses the good old numbers table:
SELECT TOP 100 number = IDENTITY(INT, 1, 1)
INTO #numbers
FROM syscomments a1
CROSS JOIN syscomments a2
ALTER TABLE #numbers
ADD CONSTRAINT pk_number
PRIMARY KEY CLUSTERED (number)
DECLARE @.string VARCHAR(20)
DECLARE @.letter CHAR(1)
SELECT @.string = 'THIS IS A TEST'
SELECT @.letter = 'S'
SELECT @.letter AS letter, COUNT(*) AS occurrences
FROM #numbers n
WHERE SUBSTRING(@.string, n.number, 1) = @.letter
DROP TABLE #numbers
"AC" <anchi.chen@.gmail.com> wrote in message
news:1150341230.216963.54010@.g10g2000cwb.googlegroups.com...
> Hi all,
> I'm having trouble googling this problem ...
> Would anyone know the easiest way to obtain the number of times a
> character appeared in a given string?
>
> Thanks
> AC
>|||Thanks Mike. I'll give that a try.
Thanks again.
AC|||DECLARE @.foo VARCHAR(64);
DECLARE @.c VARCHAR(1);
SET @.foo = 'How many qs are in this qqq qqq blat?';
SET @.c = 'q';
SELECT Number_Of_Qs = LEN(@.foo) - LEN(REPLACE(@.foo, @.c, ''));
"AC" <anchi.chen@.gmail.com> wrote in message
news:1150341230.216963.54010@.g10g2000cwb.googlegroups.com...
> Hi all,
> I'm having trouble googling this problem ...
> Would anyone know the easiest way to obtain the number of times a
> character appeared in a given string?
>
> Thanks
> AC
>|||This is such a smart and short solution.
Thanks Aaron
Aaron Bertrand [SQL Server MVP] wrote:

> DECLARE @.foo VARCHAR(64);
> DECLARE @.c VARCHAR(1);
> SET @.foo = 'How many qs are in this qqq qqq blat?';
> SET @.c = 'q';
> SELECT Number_Of_Qs = LEN(@.foo) - LEN(REPLACE(@.foo, @.c, ''));
>
>

Count CHAR(11) in a string

Hi
I need to cound the number of CHAR(11) charactors in a string.
I am currently attempting to use: len(string) - len(replace(string,
CHAR(11), ''))
But it seems to return far too many
Any help would be much appreciated
Thanks
B> But it seems to return far too many
Can you give an example?
SELECT LEN('foo') - REPLACE(LEN('foo'), CHAR(11), '')
returns 0...|||select dbo.OCCURS2 (string, CHAR(11))
CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
@.cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@.cSearchExpression) > 0
then ( datalength(@.cExpressionSearched)
- datalength(replace(cast(@.cExpressionSear
ched as
nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@.cSearchExpression
as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@.cSearchExpression)
else 0
end
end
GO
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wcon...e~2,54,33,27115
Please, download the file
http://www.universalthread.com/wcon...treme~2,2,27115
With the best regards,
Igor.
"Ben" wrote:

> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||hi
just try this:
its same as ur implementation:
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'
select len(@.ch) - len (replace(@.ch,char(11),''))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Ben" wrote:

> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||Chandra,
Please correct me if I am wrong but your solution may only work for one
occurrence of char(11).
I tried the following and still got 1 instead of 2.
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select len(@.ch) - len (replace(@.ch,char(11),''))
http://zulfiqar.typepad.com
BSEE, MCP
"Chandra" wrote:
> hi
> just try this:
> its same as ur implementation:
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'
> select len(@.ch) - len (replace(@.ch,char(11),''))
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Ben" wrote:
>|||Zulfiqar,
You've declared @.ch so that it can only hold 10 characters.
So the value you assigned to @.ch is truncated to
'ABC'+ CHAR(11) + 'DEFjh'
and this does in fact have only one char(11) value.
If you change the varchar(10) declaration to varchar(20) or larger,
you will get the result 2.
Steve Kass
Drew University
ZULFIQAR SYED wrote:

>Chandra,
>Please correct me if I am wrong but your solution may only work for one
>occurrence of char(11).
>I tried the following and still got 1 instead of 2.
>declare
>@.ch varchar(10)
>set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
>select len(@.ch) - len (replace(@.ch,char(11),''))
>
>|||Try:
declare
@.ch varchar(20)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select '*' + @.ch + '*', len(@.ch), len (replace(@.ch,char(11),''))
Since @.ch was varchar(10):
ABCEFjhi
123456790
The char(11) fell off of the end.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ZULFIQAR SYED" <DRSQLnospam2005@.hotmail.com> wrote in message
news:C0F8C537-9633-4880-831F-1497766F6870@.microsoft.com...
> Chandra,
> Please correct me if I am wrong but your solution may only work for one
> occurrence of char(11).
> I tried the following and still got 1 instead of 2.
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
> select len(@.ch) - len (replace(@.ch,char(11),''))
> --
> http://zulfiqar.typepad.com
> BSEE, MCP
>
> "Chandra" wrote:
>|||Sounds like another case for repeating the "string or binary data would be
truncated" error on invalid variable assignments. I wonder how often this
happens in the real world and people have no idea they're losing data.
"Steve Kass" <skass@.drew.edu> wrote in message
news:u$k1WGfqFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Zulfiqar,
> You've declared @.ch so that it can only hold 10 characters.
> So the value you assigned to @.ch is truncated to
> 'ABC'+ CHAR(11) + 'DEFjh'
> and this does in fact have only one char(11) value.
> If you change the varchar(10) declaration to varchar(20) or larger,
> you will get the result 2.
> Steve Kass
> Drew University
> ZULFIQAR SYED wrote:
>|||Thank you everyone
Igor2004's solution worked perfectly.
Thanks again
B
"Igor2004" <Igor2004@.discussions.microsoft.com> wrote in message
news:D82A4BEE-4EC9-44DF-BD41-1A0E3D061EC3@.microsoft.com...
> select dbo.OCCURS2 (string, CHAR(11))
> CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
> @.cExpressionSearched nvarchar(4000))
> returns smallint
> as
> begin
> return
> case
> when datalength(@.cSearchExpression) > 0
> then ( datalength(@.cExpressionSearched)
> - datalength(replace(cast(@.cExpressionSear
ched as
> nvarchar(4000)) COLLATE Latin1_General_BIN,
> cast(@.cSearchExpression
> as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
> / datalength(@.cSearchExpression)
> else 0
> end
> end
> GO
> For more information about string UDFs Transact-SQL please visit the
> http://www.universalthread.com/wcon...e~2,54,33,27115
> Please, download the file
> http://www.universalthread.com/wcon...treme~2,2,27115
> With the best regards,
> Igor.
>
> "Ben" wrote:
>

Count "SPECIFIC" characters in a string

How do I count the number of specific characters in a string ?

Example:

declare @.var as varchar(50)
set @.var="1abc1efg1"

If I wanted to count the "1"...I'll get "3" for answer.
This could maybe done by using a while loop, but is there any T-SQL command for this???http://www.dbforums.com/t648578.html

select len(YourCol)-len(replace(YourCol,'1',''))