0

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 Jan 12 at 20:23
  • @stickybit there is no such case – sakit Jan 12 at 20:27
0

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;
  • thanks for your response. But I search that is there any predefined functions in Postgres like window functions. – sakit Jan 12 at 20:37
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.