Counting data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting data

Author

Topic

alltwen
Starting Member

1 Post

Posted - 2010-07-13 : 02:08:29

I have written a report, but now need to count for example how many lines begin with AR, and how many begin with BJ. How would I do this please?

Carol

kfluffie
Posting Yak Master

103 Posts

Posted - 2010-07-13 : 02:15:18

Hi,
How does your structure look like and how does the scripts that you tested so far looks like?

Best regards,
KFluffie

Sachin.Nand

2937 Posts

Posted - 2010-07-13 : 03:11:56

[code]
select count(yourcolumn) from yourtable
where linecolumn like '[AR^BJ]%'
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 03:21:24

where linecolumn like '[AR^BJ]%'

Errmmm ... I don't think so! Thats "starts with any of the characters A, R, "^", B or J"


SELECT 'AR', COUNT(*)
FROM MyTable
WHERE MyColumn LIKE 'AR%'
UNION ALL
SELECT 'BJ', COUNT(*)
FROM MyTable
WHERE MyColumn LIKE 'BJ%'


SELECT SUM(CASE WHEN MyColumn LIKE 'AR%' THEN 1 ELSE 0 END) AS [Count_AR],
       SUM(CASE WHEN MyColumn LIKE 'BJ%' THEN 1 ELSE 0 END) AS [Count_BJ]
FROM MyTable
WHERE    MyColumn LIKE 'AR%'
      OR MyColumn LIKE 'BJ%'

Sachin.Nand

2937 Posts

Posted - 2010-07-13 : 03:35:55

quote:
Originally posted by Kristen

where linecolumn like '[AR^BJ]%'

Errmmm ... I don't think so! Thats "starts with any of the characters A, R, "^", B or J"

Oops.You are right.But I wonder is it a bug?


declare @tbl as table(val varchar(5))
insert into @tbl
select 'a1' union all
select 'a2' union all
select 'b3' union all
select 'b4' union all
select 'c1' 

select * from @tbl where val like '[a^b]%'

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 03:45:35

Why should that be a bug?
It is giving all records starting with 'a' or '^' or 'b'

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-07-13 : 04:57:45

quote:
Originally posted by webfred

Why should that be a bug?
It is giving all records starting with 'a' or '^' or 'b'

No, you're never too old to Yak'n'Roll if you're too young to die.

try this


declare @tbl as table(val varchar(5))
insert into @tbl
select 'a001' union all
select 'a2' union all
select 'b3' union all
select 'b4' union all
select 'c1' 

select * from @tbl where val like '[a1^b]%'

It should get all the records starting with a1 & b.But the resulset includes a001 also.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 05:00:56

I don't believe that.
It should return all records starting with 'a' or '1' or '^' or 'b'

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 05:04:07

Square Brackets [] are used to search any one char within a set or range of characters.
Caret (^) is working as square bracket but in reverse way so [^a-c] means include all characters except the a,b or c
But only if the caret is the leading character in the brackets.

No, you're never too old to Yak'n'Roll if you're too young to die.

connexion
Starting Member

2 Posts

Posted - 2010-07-13 : 05:15:17

hi alltwen,
can you please give some sample data and sample report of what you are looking for. This will be helpful in developing the query.

Sachin.Nand

2937 Posts

Posted - 2010-07-13 : 05:21:43

quote:
Originally posted by webfred

Square Brackets [] are used to search any one char within a set or range of characters.
Caret (^) is working as square bracket but in reverse way so [^a-c] means include all characters except the a,b or c
But only if the caret is the leading character in the brackets.
No, you're never too old to Yak'n'Roll if you're too young to die.

Got that.Thx

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-16 : 11:51:14

quote:

Originally posted by Kristen

where linecolumn like '[AR^BJ]%'

Errmmm ... I don't think so! Thats "starts with any of the characters A, R, "^", B or J"


SELECT 'AR', COUNT(*)
FROM MyTable
WHERE MyColumn LIKE 'AR%'
UNION ALL
SELECT 'BJ', COUNT(*)
FROM MyTable
WHERE MyColumn LIKE 'BJ%'


SELECT SUM(CASE WHEN MyColumn LIKE 'AR%' THEN 1 ELSE 0 END) AS [Count_AR],
       SUM(CASE WHEN MyColumn LIKE 'BJ%' THEN 1 ELSE 0 END) AS [Count_BJ]
FROM MyTable
WHERE    MyColumn LIKE 'AR%'
      OR MyColumn LIKE 'BJ%'


SELECT LEFT(MyColumn,2),COUNT(LEFT(MyColumn,2))       
FROM MyTable
WHERE    MyColumn LIKE 'AR%'
      OR MyColumn LIKE 'BJ%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources