Counting rows returned from a query

Question

Counting rows returned from a query

jason_brown

SSC Enthusiast

Points: 158
More actions
October 14, 2008 at 6:09 am

#213994

Hi all,
wonder if someone could help me here.
The following query
SELECT H.NBname, H.DNSname, CAST(H.nField0 as varchar(3)) + '.' + CAST(H.nField1 as varchar(3))+ '.' + CAST(H.nField2 as varchar(3))+ '.' + CAST(H.nField3 as varchar(3)) AS 'IPAddress', F.FaultlineID -- V.name AS 'Name' --, V.Description
FROM Vulnsfound F, Hosts H, Vulns V
WHERE F.customerID = 2
AND F.configurationID = 47
AND F.jobID = 29
AND F.hostID = H.HostID
AND F.FaultlineID = V.FaultlineID
GROUP BY H.NBname, H.DNSname, F.FaultlineID, H.nField0, H.nField1, H.nField2, H.nField3, V.name
ORDER BY H.nField0, H.nField1, H.nField2, H.nField3
Gives me
DAVE2DAVE2192.168.1.65864
DAVE2DAVE2192.168.1.654643
XP8xp8192.168.1.76864
VM-EPO40PAVM-EPO4PA192.168.1.78571
VM-EPO40PAVM-EPO4PA192.168.1.78864
etc
How do I go about sumarising this to show this...
DAVE2DAVE2192.168.1.652
XP8xp8192.168.1.761
VM-EPO40PAVM-EPO4PA192.168.1.782
i.e. the last column is replaced to represent how many different rows exist where the first three items are common in the row. E.g. the rows DAVE2DAVE2192.168.1.65864 and DAVE2DAVE2192.168.1.654643 want to be changed to show (in a simgle row) that two rows exist with these values?
Do this make sense?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

SELECT H.NBname, H.DNSname, CAST(H.nField0 as varchar(3)) + '.' + CAST(H.nField1 as varchar(3))+ '.' + CAST(H.nField2 as varchar(3))+ '.' + CAST(H.nField3 as varchar(3)) AS 'IPAddress', Count(F.FaultlineID)

FROM Vulnsfound F, Hosts H, Vulns V

WHERE F.customerID = 2

AND F.configurationID = 47

AND F.jobID = 29

AND F.hostID = H.HostID

AND F.FaultlineID = V.FaultlineID

GROUP BY H.NBname, H.DNSname, H.nField0, H.nField1, H.nField2, H.nField3, V.name

ORDER BY H.nField0, H.nField1, H.nField2, H.nField3

I removed f.faultlineid from the group by and changed it in the select clause to count(f.faultlineid)

MVDBA

jason_brown SSC Enthusiast Points: 158 More actions · Answer 2

Thanks Mike,

I'd managed to get there myself (just about) but I'm really impressed with the responsiveness of the forum.

Thanks again for the input,

Jason

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

can i suggest the following is slightly more up to date SQL

SELECT H.NBname, H.DNSname, CAST(H.nField0 as varchar(3)) + '.' + CAST(H.nField1 as varchar(3))+ '.' + CAST(H.nField2 as varchar(3))+ '.' + CAST(H.nField3 as varchar(3)) AS 'IPAddress', Count(F.FaultlineID)

FROM Vulnsfound F

INNER JOIN Hosts H ON F.hostID = H.HostID

INNER JOIN Vulns V ON F.FaultlineID = V.FaultlineID