Post reply

Simple Aggregate Function Question [Novice]

  • July 17, 2011 at 1:02 pm

    #239887

    I am trying to summarize the following data by two dimensions (VULNID AND MONTH)

    DATE IP vulnidportprotofirstFound

    6/20/11 9:10 PM172.0.0.159992123udp4/25/11 9:49 PM

    6/20/11 9:10 PM172.0.0.2523220icmp4/25/11 9:49 PM

    6/20/11 9:10 PM172.0.0.353944520tcp6/20/11 9:10 PM

    6/20/11 9:13 PM172.0.0.459992123udp4/25/11 9:14 PM

    6/20/11 9:13 PM172.0.0.55222420tcp4/25/11 9:14 PM

    6/20/11 9:13 PM172.0.0.6523220icmp4/25/11 9:14 PM

    6/20/11 9:13 PM172.0.0.753944520tcp6/20/11 9:13 PM

    6/20/11 9:09 PM172.0.0.859992123udp4/25/11 9:36 PM

    6/20/11 9:09 PM172.0.0.95222420tcp4/25/11 9:36 PM

    I would like to get results in the following format:

    VULNIDCOUNTMONTH

    =======+=======+=====

    59992 26

    522224 254

    and so on.....

    My attempt to accomplish this looks somthing like this:

    SELECT DISTINCT (vulnID), COUNT(vulnid) AS COUNT, MONTH(scandate) FROM [external.vuln_instances]

    GROUP BY vulnID, scandate

    ORDER by VULNID

    Unfortunately I am getting duplicate results for the vulnid and month combination.

    Please point out how I can fix the query to get the results I need.

    Thanks,

    ck

  • July 17, 2011 at 4:27 pm

    #1354012

    It might be your grouping condition needs to be modified (just guessing, though):

    SELECT DISTINCT (vulnID), COUNT(vulnid) AS COUNT, MONTH(scandate) FROM [external.vuln_instances]

    GROUP BY vulnID, MONTH(scandate)

    ORDER by VULNID

    Other than that, it's hard to tell since we don't even know the values of the scandate column nor does your expected output match your sample data in any way.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 18, 2011 at 11:54 am

    #1354383

    I would use a calendar table to do the grouping for this sort of thing. Here's a link to an article I wrote on the subject.

    http://www.sqlservercentral.com/articles/T-SQL/70482/

    Once you get the hang of it, calendar tables are quite simple to use and make your coding much simpler.

    Todd Fifield

  • July 18, 2011 at 12:36 pm

    #1354427

    Got it. Thanks for the replies and information.

  • July 18, 2011 at 12:43 pm

    #1354433

    Any chance to post your solution? "Got it" could be explained a little more 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 18, 2011 at 12:51 pm

    #1354440

    I utilized your suggestion to fix my query

    SELECT DISTINCT (vulnID), COUNT(vulnid) AS COUNT, MONTH(scandate) FROM [external.vuln_instances]

    GROUP BY vulnID, scandate

    ORDER by VULNID

    Regards,

    CK

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

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