Join two query results

I have two queries..

SELECT  Business_Unit,Count(FlexQuery.VulnInstance) AS PVulns FROM FlexQuery WHERE FlexQuery.CurrentPosture = "PERSISTENT" GROUP BY Business_Unit

and

SELECT   Business_Unit, Count(FlexQuery.VulnInstance)  AS NPVulns FROM FlexQuery WHERE FlexQuery.CurrentPosture <> "PERSISTENT" GROUP BY Business_Unit

which I simply want to join together so that the data looks like this

Business_Unit, PVulns, NPvulns

and is grouped by Business_Unit obviously.  
jclemoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
SELECT  Business_Unit,Count(FlexQuery.VulnInstance) AS PVulns FROM FlexQuery WHERE FlexQuery.CurrentPosture = "PERSISTENT" GROUP BY Business_Unit

UNION SELECT   Business_Unit, Count(FlexQuery.VulnInstance)  AS NPVulns FROM FlexQuery WHERE FlexQuery.CurrentPosture <> "PERSISTENT" GROUP BY Business_Unit

mx
BrandonGalderisiCommented:
This will work in SQL Server, should work in access.

select p.business_unit, p.PVULNS, n.NPVULNS
from (
SELECT  Business_Unit,Count(FlexQuery.VulnInstance) AS PVulns FROM FlexQuery WHERE FlexQuery.CurrentPosture = "PERSISTENT" GROUP BY Business_Unit
) p inner join (
SELECT   Business_Unit, Count(FlexQuery.VulnInstance)  AS NPVulns FROM FlexQuery WHERE FlexQuery.CurrentPosture <> "PERSISTENT" GROUP BY Business_Unit
) on n.business_unit = p.business_unit
Kevin CrossChief Technology OfficerCommented:
You can use a UNION ALL statement with the 2 queries you already have or can just write one query like this:

SELECT  Business_Unit
, SUM(CASE WHEN FlexQuery.CurrentPosture = "PERSISTENT" THEN FlexQuery.VulnInstance ELSE 0 END) AS PVulns
,  SUM(CASE WHEN FlexQuery.CurrentPosture <>"PERSISTENT" THEN FlexQuery.VulnInstance ELSE 0 END)  AS NPVulns
FROM FlexQuery
GROUP BY Business_Unit
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kevin CrossChief Technology OfficerCommented:
LOL, yes this is ACCESS -- change CASE to IIF:
SELECT  Business_Unit
, SUM(IIF(FlexQuery.CurrentPosture = "PERSISTENT", FlexQuery.VulnInstance, 0)) AS PVulns
,  SUM(IIF(FlexQuery.CurrentPosture <> "PERSISTENT", FlexQuery.VulnInstance, 0))  AS NPVulns 
FROM FlexQuery 
GROUP BY Business_Unit

Open in new window

jclemoAuthor Commented:
Thanks for the response mwvisa1,

I used the query above (but change SUM to COUNT) because I needed a count of records not the sum of the data, so I ended up with this;

SELECT FlexQuery.Business_Unit,

Count(IIf([FlexQuery].[CurrentPosture]="PERSISTENT",[FlexQuery].[VulnInstance],0)) AS PVulns,
Count(IIf([FlexQuery].[CurrentPosture]<>"PERSISTENT",[FlexQuery].[VulnInstance],0)) AS NPVulns

FROM FlexQuery

GROUP BY FlexQuery.Business_Unit;

Which compiles and returns results but the count in the PVulns field is always the same as the count in the NPvulns field.. what did I do wrong?
BrandonGalderisiCommented:
mwvisa1... I think it should be a sum of 1, not sum of the value.

Think so?
SELECT  Business_Unit
, SUM(IIF(FlexQuery.CurrentPosture = "PERSISTENT", 1, 0)) AS PVulns
,  SUM(IIF(FlexQuery.CurrentPosture <> "PERSISTENT", 1, 0))  AS NPVulns 
FROM FlexQuery 
GROUP BY Business_Unit

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
jclemo - did you try the simple UNION query @ http:#a22898766  ?

mx
jclemoAuthor Commented:
DatabaseMX -- that query works and the results are as expected but I need them in the same row.. such as

Business_Unit | PVULNS | NPVULNS
Business_Unit | PVULNS | NPVULNS
Business_Unit | PVULNS | NPVULNS
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
same row ?
jclemoAuthor Commented:
thanks all.. I ended up using BrandonGalderis adjustment of mwvisa1's query.. works like a charm.  
Kevin CrossChief Technology OfficerCommented:
Yes that is what I meant.  Good catch, Brandon.  Had to be offline for a while, so I am glad you got to the right solution...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.