Calculating Percentage value for a Group by value in Postgres

Question

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.

Hawkins

Answer 1

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

Boldface

Answer 2

You can use window functions

SELECT productname,
       totalamount,
       TO_CHAR(totalamount * 100 / SUM(totalamount) OVER (), 'fm90D00%') AS prc
FROM (
         SELECT productname,
                SUM(salesAmount) AS totalamount
         FROM sales
         GROUP BY productname
     ) AS t

At the 1 step we have subquery which generates sum by products. At the 2 step we sum all sums over all totals, to reach these - we omit partition and order key words in over function.

To check query - copy-paste result to the author's sql fiddle

Medor