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
property_plots
orders
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
CodePudding user response:
SELECT orders.plot_id,
ROUND(100*SUM(orders.no_sqyds)/MAX(property_plots.no_sqyds)) purchase_percent
FROM orders
JOIN property_plots ON orders.plot_id = property_plots.id
GROUP BY orders.plot_id
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=4c2bad2ff341ba94b3df2f278d1d7778
CodePudding user response:
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