asked on

Sql Syntax

Hello, I was wondering how I can modify the sql query I have below so that instead of setting @StatCode manully each time It would select all of the StatCodes in a table and complete the operation below for each one.

The table and field which contains the StatCode values is StatSource.StatCode.

Thanks!

Declare @StatCode int
Set @StatCode = 8
select (select COUNT(Distinct(VisitorID))+.0 
		from OutboundTraffic 
		where VisitorID in(select VisitorID 
						   from InboundTraffic 
						   where StatCode = @StatCode))/(select COUNT(*) from Stats where StatCode = @StatCode)

Open in new window

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

An approach like this may work. May have to tweak the join type to your liking, but hopefully you get the point of the derived tables.

SELECT t1.StatCode,
	CASE ISNULL(t2.sCount, 0) WHEN 0 THEN 0 
	ELSE ISNULL(t1.oCount, 0) / t2.sCount
	END AS StatCount
FROM 
(
	SELECT i.StatCode, COUNT(Distinct(o.VisitorID))+.0 As oCount
	FROM OutboundTraffic o INNER JOIN InboundTraffic i
	ON o.VisitorID = i.VisitorID
) AS t1
INNER JOIN
(
	SELECT StatCode, COUNT(*) AS sCount 
	FROM Stats
) AS t2
ON t1.StatCode = t2.StatCode

Open in new window

Kevin Cross

Sorry Angel, didn't see your post until just now.

grogo21

ASKER

mwvisa1: I got error:
Column 'InboundTraffic.StatCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

angelllll
what if (select COUNT(*) from Stats where stats.StatCode = s.StatCode) as perc = 0?

Thanks!

SOLUTION