AlvinWi AlvinWi - 2 months ago 12
SQL Question

Calculate two Select Statements in Columns as %

Is it possible to combine two Select statements into 2 separate column in one table, then divide them to become %?

Select count(*) as 'Quote with VIN'
From BSIT_ERA_RAW_DATA.dbo.QUOTE
where LEN(inv_vehicle_id) = 17


With the second Select statement below:

select count(*) as 'Number of Quote'
from BSIT_ERA_RAW_DATA.dbo.QUOTE


The expected result would be something like below:

Quote with VIN | Number of Quote | Result
-----------------------------------------
116260 | 195850 | 59.36%

Answer

You can use conditional aggregation to combine the logic from both queries into a single query:

SELECT SUM(CASE WHEN LEN(inv_vehicle_id) = 17 THEN 1 END) AS `Quote with VIN`,
       COUNT(*) AS `Number of Quote`,
       CONCAT(ROUND(100 * SUM(CASE WHEN LEN(inv_vehicle_id) = 17 THEN 1 END) / COUNT(*), 2),
              '%') AS Result
FROM BSIT_ERA_RAW_DATA.dbo.QUOTE