Unique SQL Query... Help! - 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

Unique SQL Query... Help!

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-14 : 16:46:24

I have a field in my table named x_CFSNUM that is filled with various 4 digit numbers- ex: 0067, 0068, 0097, etc. These are not unique.

I would like to write a query that will tell me the sum of the times the highest number occurs.

For Example: If these were the numbers: 0067, 0067, 0067, 0089, 0094, 0095- The query would produce the quantity of "3" because 0067 is listed 3 times.

I know this is confusing. Please let me know if you need any clarification.

Thanks

Mark

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-14 : 17:00:57

Something like this?

declare @a table ( c char(4) )

insert @a ( c )
select '0085' union all
select '0044' union all
select '0056' union all
select '0135' union all
select '0055' union all
select '0055' union all
select '0056' union all
select '0085' union all
select '0085'

select top 1 c, count(*) from @a
group by c
order by count(*) desc

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-14 : 17:09:31

Thanks for the try, but these CFSNUMs are auto-assigned. Perhaps if I try to explain more clearly what I am trying to do, we can come up with the correct code for it.

I have a web based intranet application that runs off an Access database. It basically tracks (updates in 60 second intervals) what units we have assigned to calls for service. What I'm trying to create is a system that will automatically notify a select group via email when the total numbers of Units assigned to one call reaches a certain threshold. For example, if 5 Units are assigned to call number 0087, then an email will go out.

I have the email system built using ASP. But I need a way in SQL to distinguish if > 5 Units are assigned to 1 call....

Does this help?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-14 : 17:16:26

This will give you any group id with > 5 instances in the table.

declare @a table ( c char(4) )

insert @a ( c )
select '0085' union all
select '0044' union all
select '0056' union all
select '0135' union all
select '0055' union all
select '0055' union all
select '0056' union all
select '0085' union all
select '0085' union all
select '0085' union all
select '0085' union all
select '0085'

select c, count(*) from @a
group by c
having count(*) > 5
order by count(*) desc

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-14 : 17:33:24

I dont know what the four digits will be... they are auto assigned...

I know I can get the number of unique calls with this code "SELECT DISTINCT CFSNUM FROM WEBASGN_FULL"

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-14 : 17:51:02

quote:
Originally posted by bobshishka

I dont know what the four digits will be... they are auto assigned...

I know I can get the number of unique calls with this code "SELECT DISTINCT CFSNUM FROM WEBASGN_FULL"

I am building the variable table and inserting records only to demonstrate the process.

The main point to gather from my post is that you can use the HAVING clause to produce your record set.

select <YourIDColumn> from <YourTable>
group by <YourIDColumn>
having count(*) > 5

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-14 : 18:11:16

Ok, this line returned a result:

SELECT count(*) FROM WEBASGN_FULL WHERE CFSNUM HAVING count(*) > 2

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-14 : 18:31:36

quote:
Originally posted by bobshishka

Ok, this line returned a result:

SELECT count(*) FROM WEBASGN_FULL WHERE CFSNUM HAVING count(*) > 2

Are you sure? The syntax is wrong.

Also, from what I understand your intent is to provide a record set of CFSNUM's that are in your table more than x number of times. Returning count(*) without providing what CFSNUM it is tied to would not supply adequate information for what (I believe) you are trying to do.

Try this:

SELECT CFSNUM FROM WEBASGN_FULL
GROUP BY CFSNUM
HAVING count(*) > 2

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-14 : 18:32:38

If you wish to include a where clause it goes here:

SELECT CFSNUM FROM WEBASGN_FULL
WHERE ...
GROUP BY CFSNUM
HAVING count(*) > 2

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-14 : 18:48:16

Thanks for your help...

This works in Access

SELECT count(*) FROM WEBASGN_FULL GROUP BY CFSNUM HAVING count(*) > 5

So how can I now get this code to display on my ASP webapp.

Total number of calls with more than 5 units assigned is = <INSERT RESULTS OF SQL QUERY>

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-14 : 19:07:54

quote:
Originally posted by bobshishka

Thanks for your help...

This works in Access

SELECT count(*) FROM WEBASGN_FULL GROUP BY CFSNUM HAVING count(*) > 2

But when I run this on my asp webapp:

set rs3 = Conn.Execute ("SELECT count(*) as rc3 FROM WEBASGN_FULL GROUP BY CFSNUM HAVING count(*) > 2")

I get his error:

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Thats getting into an area im not too skilled in (.net).

My initial thought would be that since you are passing the count to your application without naming the field it may be causing .net to complain.

Try this:

SELECT count(*) as CFSNUMcnt FROM WEBASGN_FULL GROUP BY CFSNUM HAVING count(*) > 2

If that doesnt work I would do some google searching on that specific error code and see if you cant find a solution there.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-14 : 19:52:43

A better way to ask is I want

If this is true (or it can occur)

"SELECT count(*) AS rc3 FROM WEBASGN_FULL GROUP BY CFSNUM HAVING count(*) > 6"

Then do this:

hen

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

' send one copy with Google SMTP server (with autentication)
schema = "http://schemas.microsoft.com/cdo/configuration/"
Flds.Item(schema & "sendusing") = 2
Flds.Item(schema & "smtpserver") = "smtp.gmail.com"
Flds.Item(schema & "smtpserverport") = 465
Flds.Item(schema & "smtpauthenticate") = 1
Flds.Item(schema & "sendusername") = "mymail@gmail.com"
Flds.Item(schema & "sendpassword") = "mypassword"
Flds.Item(schema & "smtpusessl") = 1
Flds.Update

With iMsg
.To = emailaddr
.From = "Myname <myemail@mydomain.com>"
.Subject = "Test send with gmail account"
.HTMLBody = message
.Sender = "Myname"
.Organization = "Myname"
.ReplyTo = "myemail@mydomain.com"
Set .Configuration = iConf
SendEmailGmail = .Send
End With

set iMsg = nothing
set iConf = nothing
set Flds = nothing
%>

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-15 : 10:26:42

bump.... I really need help on this.... Thanks!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-15 : 10:31:16

quote:
Originally posted by bobshishka

bump.... I really need help on this.... Thanks!

Are you still getting the ADODB.Field error '80020009'?

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-15 : 11:47:26

Yes I am . I think I need to include add "If Not rs.EOF" in there somewhere...

So basically I have the SQL Query I need and the ASP script I want to execute if there is a positive value to the SQL query. Now I need to put it all together.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-15 : 11:58:21

quote:
Originally posted by bobshishka

Yes I am . I think I need to include add "If Not rs.EOF" in there somewhere...

So basically I have the SQL Query I need and the ASP script I want to execute if there is a positive value to the SQL query. Now I need to put it all together.

Good, it sounds like you're well on your way. If you need anything else just let me know.

"...database development, while a serious pursuit and vitally important to business, should be fun!"
-Adam Machanic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources