how do you write PostgreSQL query for getting group of sum of records that have the same value-CodePudding

Suppose I have the following table

id | items   |  price  |
  1| item_1  |  15.0   |
  2| item_2  |  10.0   |
  3| item_1  |  15.0   |
  4| item_1  |  15.0   |
  5| item_2  |  10.0   |
  6| item_2  |  10.0   |
  7| item_3  |  25.0   |

and I want the following output

 items   |  price  | count | sum  |
 item_1  |  15.0   | 3     | 45.0 |
 item_2  |  10.0   | 3     | 30.9 |
 item_3  |  25.0   | 1     | 25.0 |

CodePudding user response：

you have to do query like this

select items, price, count(items) as count, sum (price) as sum from table1 group by items, price

CodePudding user response：

We can use analytic functions here:

WITH cte AS (
    SELECT *, SUM(price) OVER (PARTITION BY items, price) sum,
              COUNT(*) OVER (PARTITION BY items, price) count
    FROM yourTable
)

SELECT DISTINCT items, price, count, sum
FROM cte
ORDER BY items;