returning row counts on mutiple search values

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

returning row counts on mutiple search values

Author

Topic

Beaker
Starting Member

5 Posts

Posted - 2007-09-13 : 11:21:30

I need to return row counts for a list of all our users. The problem with the first query is that it doesn’t search for names within the column, it considers a list of email addresses a unique entry. I need to be able to see how many times each email address appears in the database. The second query obviously does that but I don’t want to have to copy and paste 500 usernames. the to_addr_head is a text column if that matters. Thanks!!

SELECT
Table1.to_addr_head,
COUNT(*) AS "COUNT(*)"
FROM Table1
group by Table1.to_addr_head
ORDER BY "COUNT(*)" DESC;

select count(*)
where Table1.to_addr_head like '%username%'

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 11:28:13

Please post the DDL for Table1.

Future guru in the making.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-13 : 11:32:15

Hi Beaker,

start with this

Select T1.to_addr_head,
( Select count(*) From Table1 Where PatIndex('%' + T1.to_addr_head + '%' , to_addr_head) > 0) as 'Count'
From Table1 T1
Order By Count

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:35:11

Remove the double quotes in the order by part.

SELECT
Table1.to_addr_head,
COUNT(*) AS "COUNT(*)"
FROM Table1
group by Table1.to_addr_head
ORDER BY COUNT(*) DESC;

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-13 : 11:46:36

Hi Peso,

what is wrong if we use quotes in order by.

Declare @t table ( i Int)

Insert Into @t
Select 2 Union All
Select 4 Union All
Select 1 Union All
Select 3 Union All
Select 7

Select i, i as "Id"
From @t
Order By "Id"

OutPut
----------------------

i Id
----------- -----------
1 1
2 2
3 3
4 4
7 7

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:51:07

Beacuse it treats all order by records as the string "id", and thus there is no real meaning.
It is the same as doign this

SELECT * FROM Table1 ORDER BY 'Hello, my name is Peter'

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-13 : 11:53:15

Then it should return i in the order i inserted.
Why it is ordered.

Beaker
Starting Member

5 Posts

Posted - 2007-09-13 : 11:54:37

I'm not a DBA and I didn't sleep at a Holiday Inn express last night so please bear with me as I try to work through this.

I forgot to mention this is a mysql database, slight omission sorry guys.

PeterNeo, I replaced patindex with position, it's throwing the following error.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select count(*) From Table1 Where POSITION('%' +

Peso, that returns the same results as with the double quotes.

Zoroaster, to show my inexperience... I'm not sure exactly what you are looking for. it's mysql and the table contains mostly text (notnull) columns.

I really appreciate this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:55:56

Post some proper and accurate sample data.

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-13 : 11:59:20

Hi Peso,
I am new to SQL dont mind if this is silly question, but i want to know

Declare @t table ( i Int)

Insert Into @t
Select 2 Union All
Select 4 Union All
Select 1 Union All
Select 3 Union All
Select 7

Select i
From @t

OutPut
-------------

i
-----------
2
4
1
3
7

Select i As "Id"
From @t
Order By "Id"

OutPut
---------------------
Id
-----------
1
2
3
4
7

Beaker
Starting Member

5 Posts

Posted - 2007-09-13 : 12:14:26

ok in the table I am searching I would be searching on the to_addr_head column (text, notnull)looking for User2

the column will contain either a single email address or a list separated by commas.
none of the other columns matter, everything I need is in that column I just don't know how to get it without a ton of manual labor.

so if I run select * from table1 and look at the to_addr_head column I would get the following
row 1 contains this list

user1@irisusainc.com, user2@comcast.net, user3@aol.com,user4@aol.com, user5@verizon.net, user6@aol.com, user7@asmnet.com,

and row 2 contains
user2@comcast.net

there are 5700 rows and it's a mix of single entries and multiple entries.

obviously I can't post valid email address here so I have changed those.

I hope this helped.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 12:20:21

Are you using SQL Server 2005 or SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

Beaker
Starting Member

5 Posts

Posted - 2007-09-13 : 13:14:16

It's MySQL

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 13:17:37