Two results

  • I am looking to see the count of IDS, for both the sub categories separately . But don't know how to get it in one query

    Here is the query.

    SELECT

    I.Configuration_Item ,

    count(I. ID) CountID,

    I.Sub_Category ,

    FROM ConfigIssues I WHERE

    Configuration_Item like '%outlook%' and Sub_Category like '%Emails %'

    ---OR

    ---Configuration_Item like '%outlook%' and Sub_Category like '%Managers %'

    Group By Configuration_Item, Sub_Category

    Output expected

    Configuration_Item COUNTID SubCategory

    Outlook 3 Emails

    Outlook 4 Manager

  • What's wrong with you current query? It seems to work as expected. Maybe you're having problems with your WHERE clause, try using some parenthesis.

    Luis C.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • The problem with the current query is that it gives me a total of both where clauses , where as I need it separately as per the subcategory

    Output expected:

    Configuration_Item COUNTID SubCategory

    Outlook 3 Emails

    Outlook 4 Manager

  • To know what's happening, we need you to post DDL and sample data in a consumable format (insert into statements). The reason for this is that we can't see what you see and we can't find out what's happening.

    Luis C.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • sharonsql2013 (1/30/2014)


    I am looking to see the count of IDS, for both the sub categories separately . But don't know how to get it in one query

    Here is the query.

    SELECT

    I.Configuration_Item ,

    count(I. ID) CountID,

    I.Sub_Category ,

    FROM ConfigIssues I WHERE

    Configuration_Item like '%outlook%' and Sub_Category like '%Emails %'

    ---OR

    ---Configuration_Item like '%outlook%' and Sub_Category like '%Managers %'

    Group By Configuration_Item, Sub_Category

    Output expected

    Configuration_Item COUNTID SubCategory

    Outlook 3 Emails

    Outlook 4 Manager

    Hi Sharon, possibly a Union All would help clean it up? I'm new at this, so take with a grain of salt.

    SELECT i.Configurtation_Item, COUNT(i.ID) AS CountID, I.Sub_Category

    FROM ConfigIssues AS i

    WHERE Configuration_Item LIKE '%outlook%' AND Sub_Category LIKE '%Emails %'

    GROUP BY Configuration_Item, Sub_Category

    UNION ALL

    SELECT i.Configurtation_Item, COUNT(i.ID) AS CountID, I.Sub_Category

    FROM ConfigIssues AS i

    WHERE Configuration_Item LIKE '%outlook%' AND Sub_Category LIKE '%Managers %'

    GROUP BY Configuration_Item, Sub_Category

  • Wouldn't OR'ing the two filters together do it?

    SELECT i.Configurtation_Item, COUNT(i.ID) AS CountID, I.Sub_Category

    FROM ConfigIssues AS i

    WHERE Configuration_Item LIKE '%outlook%' AND (Sub_Category LIKE '%Emails %' OR Sub_Category LIKE '%Managers %')

    GROUP BY Configuration_Item, Sub_Category;

  • Wouldn't OR'ing the two filters together do it?

    SELECT i.Configurtation_Item, COUNT(i.ID) AS CountID, I.Sub_Category

    FROM ConfigIssues AS i

    WHERE Configuration_Item LIKE '%outlook%' AND (Sub_Category LIKE '%Emails %' OR Sub_Category LIKE '%Managers %')

    GROUP BY Configuration_Item, Sub_Category;

    Oops!!! Sorry about the double-post... was watching the disaster in NJ... (the superbowl)

  • pietlinden (2/2/2014)


    Wouldn't OR'ing the two filters together do it?

    SELECT i.Configurtation_Item, COUNT(i.ID) AS CountID, I.Sub_Category

    FROM ConfigIssues AS i

    WHERE Configuration_Item LIKE '%outlook%' AND (Sub_Category LIKE '%Emails %' OR Sub_Category LIKE '%Managers %')

    GROUP BY Configuration_Item, Sub_Category;

    Oops!!! Sorry about the double-post... was watching the disaster in NJ... (the superbowl)

    Ya, I saw that she had a version like this to start so I made the UNION ALL modification... but I agree with you that OR'ing would likely work better.

Viewing 8 posts - 1 through 7 (of 7 total)

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