• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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.  
0
jclemo
Asked:
jclemo
  • 3
  • 3
  • 3
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
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
0
 
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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

0
 
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?
0
 
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

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
jclemo - did you try the simple UNION query @ http:#a22898766  ?

mx
0
 
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
same row ?
0
 
jclemoAuthor Commented:
thanks all.. I ended up using BrandonGalderis adjustment of mwvisa1's query.. works like a charm.  
0
 
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...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now