Question 90663 in PostgreSQL Question posted in
The official documentation can be found here.

Show the percentage of companies with and without crowdfunding – Postgresql

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%
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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  ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search