postgresqlgroup-bypercentage

Calculating Percentage value for a Group by value in Postgres


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.


Solution

  • 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