Show the percentage of companies with and without crowdfunding-CodePudding

I want to show the percentage of companies with crowdfunding vs. companies without crowdfunding. I tried below but I just can't get it to work.

WITH with_crowdfunding AS
    (SELECT COUNT(market) 
    FROM investment_finance
    WHERE equity_crowdfunding > 0),
no_crowdfunding AS
    (SELECT COUNT(market) 
    FROM investment_finance
    WHERE equity_crowdfunding = 0),
total_companies AS
    (SELECT COUNT(market)total_companies
    FROM investment_finance)
SELECT
    market,ROUND(with_crowdfunding/total_companies,0)* 100 percent
    FROM investment_finance;

expected:

       ?              COUNT          PERCENTAGE
with_crowdfunding         5             5%
no_crowdfunding         100            95%

CodePudding user response：

Virtual tables are being created by the WITH statement, with_crowdfunding for example. Virtual tables need to have their columns referenced in the select statement, such as: with_crowdfunding.count. The way the query in your question is written there isn't anything to join the virtual tables on, so UNION on the select statement needs to be used.

One possible solution is

WITH with_crowdfunding AS
    (SELECT COUNT(market) 
    FROM investment_finance
    WHERE equity_crowdfunding > 0),
no_crowdfunding AS
    (SELECT COUNT(market) 
    FROM investment_finance
    WHERE equity_crowdfunding = 0),
total_companies AS
    (SELECT COUNT(market) total_companies
    FROM investment_finance)
SELECT
   'With crowdfunding' market, 
ROUND((with_crowdfunding.count/total_companies.total_companies) * 100 ,0) percent
    FROM with_crowdfunding, total_companies
UNION
SELECT
  'No Crowdfunding' market, 
ROUND((no_crowdfunding.count/total_companies.total_companies  * 100),0) percent
    FROM no_crowdfunding, total_companies

CodePudding user response：

WITH with_crowdfunding AS
    (SELECT cast(COUNT(market) as varchar(128))
    FROM investment_finance
    WHERE equity_crowdfunding > 0),
no_crowdfunding AS
    (SELECT cast(COUNT(market) as varchar(128))
    FROM investment_finance
    WHERE equity_crowdfunding = 0),
total_companies AS
    (SELECT COUNT(market)total_companies
    FROM investment_finance)
SELECT
    market,ROUND(with_crowdfunding/total_companies,0)* 100 percent
    FROM with_crowdfunding,no_crowdfunding  ;