Postgresql – sum only different id’s values with any aggreagte function in postgresql

aggregategroup bypostgresql-9.6sumwindow functions

I need your help. Let's assume that we have a table like below

+---------------+-------+--------+----------+
|    barcode    | shop  |   id   | entrance |
+---------------+-------+--------+----------+
| 2014708941747 | shop1 |   3587 |        2 |
| 2014708941747 | shop2 |   3587 |        2 |
| 2014708941747 | shop3 |   3587 |        2 |
| 2014708941747 | shop4 |   3587 |        2 |
| 2014708941747 | shop5 |   3587 |        2 |
| 2014708941747 | shop6 |   3587 |        2 |
| 2014708941747 | shop7 |   3587 |        2 |
| 2014708941747 | shop1 |  44791 |        2 |
| 2014708941747 | shop8 |  65846 |        0 |
| 2014708941747 | shop9 |  83246 |        0 |
| 2014708941747 | shop3 |  92705 |       22 |
| 2014708941747 | shop4 |  98014 |        8 |
| 2014708941747 | shop2 | 103612 |       12 |
| 2014708941747 | shop5 | 109961 |       19 |
| 2014708941747 | shop6 | 115025 |        6 |
| 2014708941747 | shop7 | 126898 |      144 |
+---------------+-------+--------+----------+

Now I want to know how many quantities exist for the given barcode but id must not be repeated. With the above example, our result must be like below

+---------------+----------+
|    barcode    | entrance |
+---------------+----------+
| 2014708941747 |      225 |
+---------------+----------+

If I do this with a traditional group by entrance=227 which is not correct. Is there any aggregate function to solve this kind of problem?

We can solve this problem with an inner query and group by combination. But I want to know can we solve this with window functions or not?

Best Answer

If entrance is always the same per barcode and ID you could first group by barcode and id taking the max entrance and then get the sum grouping by barcode.

SELECT x.barcode,
       sum(x.entrance) entrance
       FROM (SELECT t.barcode,
                    t.id,
                    max(t.entrance) entrance
                    FROM elbat t
                    GROUP BY t.barcode,
                             t.id) x
       GROUP BY x.barcode;
Related Question