MySQL group by percent with where clause-CodePudding

i have a table Products with following fields and values:

  ID | Productname | Productprice | Supplier | Available |
 ---- ------------- -------------- ---------- ----------- 
|  1 | Tshirt      | 20           | CompanyA |    Yes    |
|  2 | Pants       | 45           | CompanyA |    Yes    |
|  3 | Shoes       | 95           | CompanyB |    Yes    |
|  4 | Socks       | 12           | CompanyA |     No    |
|  5 | Trainer     | 50           | CompanyA |    Yes    |

How to query how many products belong to CompanyA with status Available=Yes in percentage?

CodePudding user response：

To get the percentage of available products appearing in CompanyA from the overall products, you may use a subquery to calculate that.

SELECT COUNT(*) * 100 / (SELECT COUNT(*) FROM `products`) as `percentage`
FROM `products` WHERE `supplier` = 'CompanyA' and `available` = 'Yes'

Based on your data sample, the above query should return

   percentage
 ----- ------- 
|   60.0000   |

CodePudding user response：

Considering that by "percent" you mean the number of rows with Available among all rows of that company:

select Supplier, TotalAvailable / Total from(
   select 
      Supplier, 
      sum( if(Available = 'Yes',1,0) ) as TotalAvailable, 
      count(*) as Total
   from
      Products
   where
      Supplier = 'CompanyA'
   group by
      Supplier
) a

Or you can use

select 
   Supplier, 
   sum( if(Available = 'Yes',1,0) ) / count(*) as Percent 
from
   Products
where
   Supplier = 'CompanyA'
group by
   Supplier

Mind that you must handle the "Percent" as it fits for your intentions: multiply by 100, cut the decimals for representation etc.