I want to calculate Percentage of a value in Group by. My Table data
salesId salesAmount productName salesTyp
------- ----------- ----------- --------
1 50.00 mouse online
2 100.00 mouse shop
3 150.00 mouse shop
4 50.00 mouse shop
5 100.00 keyboard shop
6 50.00 keyboard online
The Out put i want to show like
productName totalamount percentageofonline
----------- ----------- ------------------
mouse 350 25.00
keyboard 150 50.00
Here 4 mouse was sold (3 in shop and 1 in Online) so the percentage is 25.00
and 2 keyboard was sold (1 in shop and one in online) so the percentage is 50.00
Please help me to get it. I have created a SQLFIDDLE for table structure.
In a quick way :
WITH total AS (
SELECT productName, sum(salesAmount) AS totalamount
FROM testSales
GROUP BY productName
)
SELECT total.totalamount, total.productName, (
SELECT count(*)
FROM testSales
WHERE salesTyp='online'
AND productName = total.productName
)::float / count(*) * 100 as percentageofonline
FROM testSales, total
WHERE testSales.productName = total.productName
GROUP BY total.productName, total.totalamount
the first subrequest computes the total amount, the subrequest is used for the percentage.
There must be a more optimized way to do it but that does the job