Help with query....

Please see query below:

SELECT COUNT(DISTINCT target) FROM iislog where target like '%answeringservice%'

The result of the query is 71, is there a way to list the values out in the output of the query.

for example: /answeringservice/default.asp = 3200
/answeringservice/deny.asp = 3700

Thanking you all in advance.

Lystrayes

SELECT DISTINCT target FROM iislog where target like '%answeringservice%'|||No

SELECT DISTINCT target, Count(target) as 'total' FROM iislog where target like '%answeringservice%'
Group By target|||the question was "is there a way to list the values out in the output of the query"

so you're saying it's not possible, white knight?

:)

by the way, when you use GROUP BY, the DISTINCT is redundant|||Yes it's possible

The code given did not do it!
sorry If I confused the issue|||White Knight, the code work well, thanks

Lystra

Help with query

I want to take this code and insert data on a monthly basis. For example all the data for month of August.

INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1
GO

ThanksChange your where clause

WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1

To this

WHERE MONTH(LogTime)=8|||If you can ride an index, I'd use:INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE '2004-08-01' <= LogTime
AND LogTime < '2004-09-01'
GOThis lets you ride an index if one exists on LogTime, which can improve your performance by literally orders of magnitude (things can take much less than one tenth as long as not riding the index).

-PatP