Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Summarizing multiple colummns from a table Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 3:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 5:31 PM
Points: 27, Visits: 123
Table:

IPADDRESSSTR, NBNAME, FaultlineID, CVE, OPEN, CLOSED, STATUS
172.0.0.1, HOST1, 3623, CVE-2013-1212, 1, 0, vulnerable
172.0.0.2, HOST2, 3624, CVE-2013-1213, 0, 1, not vulnerale
172.0.0.3, HOST3, 3624, CVE-2013-1213 0, 1, not vulnerale
172.0.0.4, HOST4, 3624, CVE-2013-1213 1, 0, not vulnerale
172.0.0.5, HOST5, 3624, CVE-2013-1213 0, 1, not vulnerale

I would like to count the values in the OPEN and CLOSED column and calculate a total for each FID and CVE combination. The output should look like this:

FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,2,3

I've tried the following SQL script, however, the counts are obviously wrong.

SELECT faultlineid, cve, SUM([OPEN]), SUM({CLOSED}),SUM([OPEN])+SUM({CLOSED}) AS TOTAL FROM TABLE
GROUP BY faultlineid, cve

How should I approach this problem in order to get accurate column counts?
Post #1486451
Posted Tuesday, August 20, 2013 3:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 3,374, Visits: 7,296
Why do you have this
FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,2,3

Instead of this?
FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,3,4



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1486453
Posted Tuesday, August 20, 2013 3:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 5:31 PM
Points: 27, Visits: 123
An error with my math:

This is correct:

FaultlinID, CVE, OPEN, CLOSED, TOTAL
3623, CVE-2013-1212, 3623, 1, 0, 1
3624, CVE-2013-1213, 3624,1,3,4

ty
Post #1486456
Posted Tuesday, August 20, 2013 3:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 3,374, Visits: 7,296
So the output from your query is correct?
The only difference I see is that you repeat the FID



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1486459
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse