July 3, 2012 at 4:58 am
Hi all,
As name says.. new and really bad a t-sql never mind the rest..
Im trying to query a table of ipaddresses and find how many free ip addresses we have per subnet. I cant work out how to do sums on the Groups instead of the whole table..
set up is below and my current attempt that just totals the whole table instead of the group
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--=====Create table
CREATE TABLE #mytable
(
Id INT IDENTITY (100,1) CONSTRAINT PK_Id_Ipaddress PRIMARY KEY
, IPAddress VARCHAR(50)
, SubnetId INT
, STATUS INT
)
--===== Input data
INSERT INTO #mytable
VALUES ( '192.168.1.1', '100', 1 ) ,
( '192.168.1.2', '100', 2 ) ,
( '192.168.1.3', '100', 1 ) ,
( '192.168.1.4', '100', 2 ) ,
( '192.168.2.1', '101', 1 ) ,
( '192.168.2.1', '101', 2 ) ,
( '192.168.2.1', '101', 2 ) ,
( '192.168.2.1', '101', 1 ) ,
( '192.168.2.1', '101', 1 ) ,
( '192.168.3.1', '102', 2 ) ,
( '192.168.3.1', '102', 1 ) ,
( '192.168.3.1', '102', 1 ) ,
( '192.168.3.1', '102', 1 )
--===== Full table results
SELECT * FROM #mytable
--===== Results trying to find Status =2 (ie not in use ip addresses) - currently counts all results
SELECT SubnetId ,
(SELECT COUNT(*) FROM #mytable WHERE STATUS = 2) AS 'Available IPs'
FROM #mytable
GROUP BY SubnetId
Id like it to end with
SubnetID - Available IPs
100 - 2
101 - 2
102 - 1
Thanks for any help..
July 3, 2012 at 5:02 am
This will do the trick
SELECT
SubnetID,
COUNT(*)
FROM
#mytable
WHERE
STATUS = 2
GROUP BY
SubnetID
July 3, 2012 at 5:12 am
sigh so simple...
My logic is fail..
Thank you sir! (again!)
July 3, 2012 at 5:20 am
Your logic was sound in theory but not in practise.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply