Two results

Question

Two results

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 30, 2014 at 10:58 am

#303664

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

rho_pooka Ten Centuries Points: 1385 More actions · Answer 4

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

pietlinden SSC Guru Points: 63509 More actions · Answer 5

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;

pietlinden SSC Guru Points: 63509 More actions · Answer 6

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)

rho_pooka Ten Centuries Points: 1385 More actions · Answer 7

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.