Divide the results of two select queries

I have two SQL count queries that on their own work fine, they are:

SELECT count(*) AS TOTAL_PROGRESS_BY_LINE_
FROM dbo.PID_Components_PROCESS_LINES
WHERE ISOGEN_LINE_PROGRESS_ = 'C'

RESULT: TOTAL_PROGRESS_BY_LINE_ = 26

SELECT count(*) AS TOTAL_LINES_BY_PROJECT_
FROM dbo.PID_Components_PROCESS_LINES
WHERE PROJECT_NUMBER_ = 'PJ001234'

RESULT: TOTAL_LINES_BY_PROJECT_ = 130

Now how to do I add to the query to get the percentage of 26/130??

I have a new query to go along with how to get percentages.

Here it is:

SELECT ISOGEN_LINE_PROGRESS_, PROJECT_NUMBER_,
CASE
    WHEN ISOGEN_LINE_PROGRESS_ = 'A' THEN 'NOT IN MODEL'
    WHEN ISOGEN_LINE_PROGRESS_ = 'B' THEN 'ROUGHED IN'
    WHEN ISOGEN_LINE_PROGRESS_ = 'C' THEN 'PARTIAL CHECK'
    WHEN ISOGEN_LINE_PROGRESS_ = 'D' THEN 'READY FOR FINAL CHECK'
    WHEN ISOGEN_LINE_PROGRESS_ = 'E' THEN '100% COMPLETE'
    WHEN ISOGEN_LINE_PROGRESS_ = '0' THEN 'ISSUE FOR CONSTRUCTION'
END AS PROGRESS_PER_LINE_
FROM PID_Components_PROCESS_LINES
WHERE PROJECT_NUMBER_ = 'PJ001234'
ORDER BY ISOGEN_LINE_PROGRESS_

this brings back results below:

ISOGEN_LINE_PROGRESS_   PROJECT_NUMBER_ PROGRESS_PER_LINE_
A                       PJ001234        NOT IN MODEL
B                       PJ001234        ROUGHED IN
C                       PJ001234        PARTIAL CHECK
D                       PJ001234        READY FOR FINAL CHECK

If I remove the Distinct from my query there are obviously multiple rows for each level of progress. How do I add to the above distinct query to have a column at the end with the rate or percent of each level of progress compared to the overall number of lines?

Answer

You may use conditional aggregation and simple division in one query:

select
  100.0 *
  count(
    case when ISOGEN_LINE_PROGRESS_ = 'C'
    then 1
    end
  )
  /
  nullif(count(
    case when PROJECT_NUMBER_ = 'PJ001234'
    then 1
    end
  ), 0) as rate_
FROM dbo.PID_Components_PROCESS_LINES
WHERE ISOGEN_LINE_PROGRESS_ = 'C'
  or PROJECT_NUMBER_ = 'PJ001234'

Advertisement

Answer