?
Solved

SQL Query to return count based on Unique IP

Posted on 2006-05-11
8
Medium Priority
?
472 Views
Last Modified: 2009-12-16
Hey fellow Experts...

Below is a picture of my table:

http://www.ctrlaltdel-usa.net/yabb_2_1_superman/YaBB.pl?num=1147393501

column names as folllows
INT             id
INT             id_newsletter_vol
INT             id_newsletter_issue
NVARCHAR  IP_address

What I would like to do is query the database and return a count based on:

id_newsletter_vol = 3
id_newsletter_issue = 37

and the IP_address must be Unique (if there are 5 instances it counts as 1, or 1000 instances, it counts as 1)
also MUST FILTER out (192.168.1.x) , This IP address is the local LAN and must not be in the count.

I need the resulting query to return as a variable m_count

Thank you in advance,

irwinks
0
Comment
Question by:Irwin Santos
  • 5
  • 2
8 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 500 total points
ID: 16663883
Try this:

select count(distinct ip_Address) from <YOUR TABLE>
where id_newsletter_vol = 3
and id_newsletter_issue = 37
and left(ip_address, 10) <> '192.168.1.'
0
 
LVL 30

Author Comment

by:Irwin Santos
ID: 16663888
BRB in 15 min
0
 
LVL 30

Author Comment

by:Irwin Santos
ID: 16663924
http://www.ctrlaltdel-usa.net/superman/report_hit_count.asp

The above is the link to the active code.  (but not working yet).

I'm drawing a blank and frankly out of practice... Need to display the result to the screen, here is my code.

-----------------------------------------------------------------------------------------
<%@LANGUAGE="VBSCRIPT"%>
<%Option Explicit%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Journal Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<%
Dim Conn, RS, SQL

Set Conn=Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
Conn.Open "Data Source=DS;user id=IUD;Password=PW;"

SQL= "SELECT COUNT(distinct ip_Address) FROM journal_log WHERE id_newsletter_vol = 3 AND id_newsletter_issue = 37 AND left(ip_address, 10) <> '192.168.1.'"

RS.Open SQL,Conn,1,2
%>
<!-- Response.Write RS("SQL")   DRAWING  A BLANK HERE !!!!-->
<%            
RS.Close
Conn.Close
Set RS=Nothing
Set Conn=Nothing
%>

</body>
</html>
-----------------------------------------------------------------------------------------
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:QPR
ID: 16663977
SQL = "SELECT COUNT(distinct ip_Address) FROM journal_log WHERE id_newsletter_vol = 3 AND id_newsletter_issue = 37 AND left(ip_address, 10) <> '192.168.1.' as TheCount"

......

<%
response.write RS("theCount")
%>

You response.write a field within the recordset.
Here I have given the count an alias so that it can be referred to.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 16664003
Actually you should have

SQL= "SELECT COUNT(distinct ip_Address) TheCount FROM journal_log WHERE id_newsletter_vol = 3 AND id_newsletter_issue = 37 AND left(ip_address, 10) <> '192.168.1.'"
rs.open sql, conn

if not rs.eof then
response.write rs("TheCount")
end if

0
 
LVL 30

Author Comment

by:Irwin Santos
ID: 16664013
http://www.ctrlaltdel-usa.net/superman/report_hit_count_2.asp

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'.

/superman/report_hit_count_2.asp, line 21

Here is the query string

SQL= "SELECT COUNT(distinct ip_Address) FROM journal_log WHERE id_newsletter_vol = 3 AND id_newsletter_issue = 37 AND left(ip_address, 10) <> '192.168.1.' AS theCount"
0
 
LVL 30

Author Comment

by:Irwin Santos
ID: 16664022
ok. that last one worked!

thank you!
0
 
LVL 30

Author Comment

by:Irwin Santos
ID: 16664028
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question