CODEWITHSUNDEEP
sqlsql-server
Vikas
Vikas

Reputation: 199

Need help in combining two queries into one

I need some help in combining two different queries into a single query; below are the queries which I'm using along with the outputs of the individual query.

Query 1:

SELECT 
   count( *) TestCount,
   asset 
FROM hsbc_csmip_dbprotect 
group by asset

Output 1:

     TestCount                       Asset
         18                     DB2MOTIV:[email protected]
         18                     IBASL01:[email protected]
         17                     ICOGP01:[email protected]
         18                     ICTIP01:[email protected]
         18                     IPSSP01:[email protected]
         16                     IMXGP01:[email protected]
         19                     IINFP01:[email protected]
          9                     IHF4P01:[email protected]
         19                     IHQAL01:[email protected]

Query 2:

SELECT 
   count( *) TestPassedCount,
   asset 
FROM hsbc_csmip_dbprotect 
where result_status = 'Not A Finding' 
group by asset

Output 2:

   TestPassedCount                Asset
          7                     DB2MOTIV:[email protected]
         16                     IBASL01:[email protected]
         11                     ICOGP01:[email protected]
         10                     ICTIP01:[email protected]
         11                     IPSSP01:[email protected]
          9                     IMXGP01:[email protected]
         17                     IINFP01:[email protected]
          9                     IHF4P01:[email protected]
         18                     IHQAL01:[email protected]

I would like to see the output as below

      TestCount          TestPassedCount        Asset
         18                      7              DB2MOTIV:[email protected]
         18                      16             IBASL01:[email protected]
         17                      11             ICOGP01:[email protected]
         18                      10             ICTIP01:[email protected]
         18                      11             IPSSP01:[email protected]
         16                       9             IMXGP01:[email protected]
         19                      17             IINFP01:[email protected]
          9                       9             IHF4P01:[email protected]
         19                      18             IHQAL01:[email protected]

Upvotes: 0

Views: 84

Answers (2)

S3S
S3S

Reputation: 25152

Since they are the same table, you can use a case statement.

SELECT 
    count( *) TestCount
    ,count(case when result_status = 'Not A Finding' then 1 end) TestPassedCount
    ,count(case when result_status = 'Not A Finding' then 1 end) / count(*) ThePercentage
    ,asset 
FROM hsbc_csmip_dbprotect
group by asset

Upvotes: 3

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can use CASE statement like below :

SELECT count(*) AS TestCount
    ,count(case when result_status = 'Not A Finding' then 1 else 0 end) AS TestPassedCount, 
    asset 
FROM hsbc_csmip_dbprotect
group by asset

Upvotes: 0

Related Questions