CODEWITHSUNDEEP
t-sql
Didier Levy
Didier Levy

Reputation: 3453

TSQL help calculate percentage

I wonder how to do this with a variable:

SELECT  Count(distinct UserIP) 
FROM VisitorIP 
G0

SELECT Country, Count(distinct UserIP) Visitors, Count(distinct UserIP) * 100 / 2865 Pct 
FROM VisitorIP group by country order by Visitors desc

Now I want to replace 2865 by the count(distinct UserIP) above.

There is a say: In IT, better one who knows than 10 who search!

Any clue welcome... Happy New Year to all geeks out there.

Upvotes: 1

Views: 2163

Answers (2)

philofinfinitejest
philofinfinitejest

Reputation: 4037

Using a variable:

DECLARE @userip_count int

SELECT @userip_count = Count(distinct UserIP) 
FROM VisitorIP 

SELECT Country, Count(distinct UserIP) Visitors, Count(distinct UserIP) * 100 / @userip_count Pct 
FROM VisitorIP group by country order by Visitors desc
GO

Moving GO is essential here, in order for the variable to remain in scope for use in the second query.

Upvotes: 3

JBrooks
JBrooks

Reputation: 10013

SELECT Country, 
Count(distinct UserIP) Visitors, 
round(Count(distinct UserIP) / 
              (SELECT  Count(distinct UserIP) 
              FROM VisitorIP) * 100,2) Pct 
FROM VisitorIP 
group by country 
order by Visitors desc

Upvotes: 0

Related Questions