Find Percentage using Aggregation Function MYSQL

user_6468244 0
Viewed 83

So I have a 3 tables properties, property_plots and orders. Each property will have many plots and each plot can have size with no_sqyds. Now users can purchase plot with quantity no_sqyds which I am storing in orders table.

properties

enter image description here

property_plots

id | property_id | no_sqyds

orders

id | property_id | plot_id | no_sqyds

So my question is how can I find the percent of purchase for particular plot ? Also how can I find the percent of purchase for entire property using same ?

So far what I tried is to find the percent at plot level using this rough query

((SELECT sum(o.no_sqyds) FROM orders as o 
WHERE o.plot_id = pp.id)*100)/pp.no_sqyds FROM property_plots as pp

Based on table orders table I can get percent for each plot but I am also looking at property level by combining this below data. (I have to get avg of this all plot percent to find at property level?)

plot_id 1 = 100% purchase

plot_id 2 = 66.67% purchase

plot_id 3 = 50%

Sample DB - https://pastebin.com/RYJwwRqJ

relation aggregate-functions mysql sql
2 Answers

Join them and group them and calculate.

SELECT 
  property_id
, prop.name AS property_name
, (SUM(order_no_sqyds)/SUM(plot_no_sqyds))*100 AS percentage
, SUM(plot_no_sqyds) AS plot_no_sqyds
, SUM(order_no_sqyds) AS order_no_sqyds
, COUNT(plot_id) AS total_plots
, SUM(total_orders) AS total_orders
FROM
(
    SELECT 
      plot.property_id
    , plot.id AS plot_id
    , plot.no_sqyds AS plot_no_sqyds
    , SUM(ordr.no_sqyds) AS order_no_sqyds
    , COUNT(DISTINCT ordr.id) AS total_orders
    FROM property_plots AS plot
    LEFT JOIN orders AS ordr
      ON ordr.plot_id = plot.id
     AND ordr.property_id = plot.property_id
    GROUP BY 
      plot.property_id
    , plot.id
    , plot.no_sqyds
) q
INNER JOIN properties AS prop
   ON prop.id = q.property_id
GROUP BY property_id, prop.name
ORDER BY property_id
property_id property_name percentage plot_no_sqyds order_no_sqyds total_plots total_orders
1 Lake View Park 66.6667 225 150 3 4

Demo on db<>fiddle here

An other way to calculate it is by joining to an aggregation of the orders.

SELECT 
  plot.property_id
, prop.name AS property_name
, (SUM(ordr.no_sqyds)/SUM(plot.no_sqyds))*100 AS percentage
, SUM(plot.no_sqyds) AS plot_no_sqyds
, SUM(ordr.no_sqyds) AS order_no_sqyds
, COUNT(DISTINCT plot.id) AS total_plots
, SUM(total_orders) AS total_orders
FROM property_plots AS plot
INNER JOIN properties AS prop
   ON prop.id = plot.property_id
LEFT JOIN (
   SELECT plot_id, property_id
   , SUM(no_sqyds) AS no_sqyds
   , COUNT(DISTINCT id) AS total_orders
   FROM orders
   GROUP BY plot_id, property_id
) AS ordr
  ON ordr.plot_id = plot.id
 AND ordr.property_id = plot.property_id
GROUP BY 
  plot.property_id
, prop.name

Plots only

SELECT 
  plot.property_id
, plot.id AS plot_id
, (SUM(ordr.no_sqyds)/plot.no_sqyds)*100 AS percentage
, plot.no_sqyds AS plot_no_sqyds
, SUM(ordr.no_sqyds) AS order_no_sqyds
, COUNT(DISTINCT ordr.id) AS total_orders
FROM property_plots AS plot
LEFT JOIN orders AS ordr
  ON ordr.plot_id = plot.id
 AND ordr.property_id = plot.property_id
GROUP BY 
  plot.property_id
, plot.id
, plot.no_sqyds
user_40034190
user_101387340
Related