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: Today @ 10:34 PM
Points: 3,749, Visits: 8,417
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Today @ 10:34 PM
Points: 3,749, Visits: 8,417
So the output from your query is correct?
The only difference I see is that you repeat the FID



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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