AlvinWi AlvinWi - 1 year ago 71
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'
where LEN(inv_vehicle_id) = 17

With the second Select statement below:

select count(*) as 'Number of Quote'

The expected result would be something like below:

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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download