sum only different id's values with any aggreagte function in postgresql

Question

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?

What if there are different values for an ID?

sticky bit
– sticky bit

2019-01-12 20:23:37 +00:00
Commented Jan 12, 2019 at 20:23 — sticky bit
– sticky bit, Commented Jan 12, 2019 at 20:23
@stickybit there is no such case

sancho
– sancho

2019-01-12 20:27:37 +00:00
Commented Jan 12, 2019 at 20:27 — sancho
– sancho, Commented Jan 12, 2019 at 20:27

sticky bit · Accepted Answer · 2019-01-12 20:33:15Z

1

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;

answered Jan 12, 2019 at 20:33

sticky bit

5,0042 gold badges15 silver badges19 bronze badges

thanks for your response. But I search that is there any predefined functions in Postgres like window functions.

sancho
– sancho

2019-01-12 20:37:59 +00:00
Commented Jan 12, 2019 at 20:37

Add a comment |

ThatDataGuy · Accepted Answer · 2019-01-12 20:59:34Z

1

Give all the values a row_number partitioned by barcode and id. Then filter all the rows that don't have a row_number = 1, and then sum. Viola

with x as (
select
*
,ROW_NUMBER() over (partition by barcode, id order by entrance asc) as row_number
from yourtable
)
select 
barcode,
sum(entrance) as entrance
from x
where row_number = 1
GROUP by barcode

answered Jan 12, 2019 at 20:59

ThatDataGuy

5786 silver badges17 bronze badges

Add a comment |

Erwin Smout · Accepted Answer · 2021-10-18 07:23:22Z

1

SELECT SUM(ENTRANCE) FROM (
  SELECT DISTINCT BARCODE, ID, ENTRANCE FROM TBL
  )

Whether "predefined functions" exist that do this is highly unlikely, given the inevitable requirement to include some kind of DISTINCT-like functionality.

answered Oct 18, 2021 at 7:23

Erwin Smout

1,75610 silver badges12 bronze badges

Add a comment |

Stack Exchange Network

sum only different id's values with any aggreagte function in postgresql

3 Answers 3

Your Answer

Hot Network Questions

sum only different id's values with any aggreagte function in postgresql

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions