SQL COUNT Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL COUNT Query

Author

Topic

latiful
Starting Member

10 Posts

Posted - 2007-03-10 : 19:39:43

Any help will be helpful for the following query

Overview:

SQL Table with data:

ID - Value - IPAddress
======================
1 - aa - 210.1
2 - aa - 210.1
3 - aa - 210.2
4 - aa - 210.1
5 - bb - 210.1
6 - bb - 210.1
7 - cc - 210.3
8 - cc - 210.4
9 - cc - 210.1

Output:

Value - TotalIPAddress - UniqueIPAddress
aa - 4 - 2
cc - 3 - 3
bb - 2 - 1

Query output explanation for aa - 44 - 2:

For "aa", IPAddress "210.1" 3 times and "210.2" 1 time, so TotalIPAddress 4 and UniqueIPAddress 2

I was trying with the following query but it is not giving me the right output for the UniqueIPAddress column. Can anybody tell me how can I fix it?

select distinct
r1.[Value],
(Select count(IPAddress) from Report1 where Value=r1.Value) as 'TotalIPAddress',
(Select count(IPAddress) from Report1 where Value=r1.Value and IPAddress=r1.IPAddress) as 'UniqueIPAddress'
from Report1 r1
Order by TotalIPAddress Desc

Thanks for any reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 20:57:07

[code]
select Value, count(IPAddress) as TotalIPAddress , count(distinct IPAddress) as UniqueIPAddress
from SQLTable
group by Value
[/code]

latiful
Starting Member

10 Posts

Posted - 2007-03-10 : 22:47:17

I just need to extend it, need two more cols:

OverallTotalIPAddress = (sum of TotalIPAddress col 4+3+2=9)

and

OverallTotalUniqueIPAddress = (sum of OverallTotalUniqueIPAddress col 2+3+1=6)

Value - TotalIPAddress - OverallTotalIPAddress - UniqueIPAddress - OverallTotalUniqueIPAddress
aa - 4 - 9 - 2 - 6
cc - 3 - 9 - 3 - 6
bb - 2 - 9 - 1 - 6

Thanks for any reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 23:11:44

[code]
select *
from
(
select Value, count(IPAddress) as TotalIPAddress , count(distinct IPAddress) as UniqueIPAddress
from SQLTable
group by Value
) a
cross join
(
select OverallTotalIPAddress = count(IPAddress),
OverallTotalUniqueIPAddress = count(distinct IPAddress)
from SQLTable
) b
[/code]

latiful
Starting Member

10 Posts

Posted - 2007-03-11 : 03:15:17

Actually you given query is not giving right value for the last col(OverallTotalUniqueIPAddress), instead of 6 it's giving 3,

i am guessing which is same as (Select count(distinct IPAddress) from Report1), but I want it will show sum of UniqueIPAddress column.

Any idea???

I have modified your given query(your help) in the following way and it's also giving same ouput as you, but still not the right value for 'OverallTotalUniqueIPAddress' column.

Any help will be appreciate. Thanks for your time.

select
Value,
count(IPAddress) as TotalIPAddress ,
(Select count(IPAddress) from Report1) as 'OverallTotalIPAddress',
count(distinct IPAddress) as UniqueIPAddress,
(Select count(distinct IPAddress) from Report1) as 'OverallTotalUniqueIPAddress'
from Report1
group by Value

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 05:13:16

[code]
-- declare table
declare @SQLTable table
(
ID int,
Value varchar(10),
IPAddress varchar(10)
)
-- Insert sample data provided into table
insert into @SQLTable
select 1 , 'aa' , '210.1' union all
select 2 , 'aa' , '210.1' union all
select 3 , 'aa' , '210.2' union all
select 4 , 'aa' , '210.1' union all
select 5 , 'bb' , '210.1' union all
select 6 , 'bb' , '210.1' union all
select 7 , 'cc' , '210.3' union all
select 8 , 'cc' , '210.4' union all
select 9 , 'cc' , '210.1'

-- Here's the query
select TotalIPAddress, OverallTotalIPAddress,
UniqueIPAddress, OverallTotalUniqueIPAddress
from
(
select Value,
count(IPAddress) as TotalIPAddress,
count(distinct IPAddress) as UniqueIPAddress
from @SQLTable
group by Value
) a
cross join
(
select OverallTotalIPAddress = count(IPAddress),
OverallTotalUniqueIPAddress = count(distinct IPAddress)
from @SQLTable
) b

/* -- RESULT
TotalIPAddress OverallTotalIPAddress UniqueIPAddress OverallTotalUniqueIPAddress
-------------- --------------------- --------------- ---------------------------
4 9 2 4
2 9 1 4
3 9 3 4
*/
[/code]

quote:

Actually you given query is not giving right value for the last col(OverallTotalUniqueIPAddress), instead of 6 it's giving 3,

Actually my query counted 4 and not 3. Can you explain how do you counted 6 ?
210.1, 210.2, 210.3, 210.4

latiful
Starting Member

10 Posts

Posted - 2007-03-11 : 06:37:53

Quote:
-------------------------------------------

/* -- RESULT
TotalIPAddress OverallTotalIPAddress UniqueIPAddress OverallTotalUniqueIPAddress
-------------- --------------------- --------------- ---------------------------
4 9 2 4
2 9 1 4
3 9 3 4
*/

Can you explain how do you counted 6 ?
-------------------------------------------

OverallTotalUniqueIPAddress = sum of the column UniqueIPAddress.
OverallTotalUniqueIPAddress = 2+1+3 = 6 (see above the result)

Thanks again for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 06:43:27

2 + 1 + 3 is not the total unique IP Address.

Total Unique IP = 210.1, 210.2, 210.3, 210.4 = 4

What you want is not really total unique IP Address. It is just a sum of total unique IP by Value.

So what do you want really ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources