Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oya2z-0005jc-VR for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 14:51:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oya1y-0007lk-Ut for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 14:50:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oya1y-0007lb-98 for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 14:50:18 +0000 Received: from mail-qk1-x731.google.com ([2607:f8b0:4864:20::731]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oya1r-0005RD-J4 for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 14:50:17 +0000 Received: by mail-qk1-x731.google.com with SMTP id x21so2793093qkj.0 for ; Fri, 25 Nov 2022 06:50:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=amS3Cabyg5ScZnoNi8bNpu+tDKyQUXrLwjnj/dctYWQ=; b=LbAfMWhdSH9wbcEeKvnVPNyFHEGEpgWLFl1a8BLLt1+ol+JJeO5L9x7uKjmAaUFbkx LB8qW2lIf6T68tJkooN8L8ci0g9OMt7n7qUEVjxliqwseuPYzA+GJA8PiYwXVGugp4Cg smhSBT2LC8vglM9vxjqA6Dh6muPzJietM0eiN4wMHZxBw3hutrykSA2G8loZ18Hy2tGM DB4w0zA4z83/Hq4gvoCOuCS94efa2BrZjRZ6Wz0D68bla9dnWtQmSX7+F3C2t5lnaVez Ldle5iq+flge5TML8oZQS0ks3jgMx2HKsG5tzxVxGnRHFCyCl/lHAncpkZChagEiRjWP jHWQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=amS3Cabyg5ScZnoNi8bNpu+tDKyQUXrLwjnj/dctYWQ=; b=Czeahidf8AZEY/y4+SqBOQN83K8EGMRY3BNrrR2SZ1JNwpOSV0qzdoediuRAx9YN/w bRfb+DcpNPF2fip8NQoTP5Ay7HUPLT4vHHWH35kvx6i5Ub/7RQFhQsswdxxBktubPdFs sGZR0nYE0/i7wT0D89tQSwgUVMhOnRL52IK7ZbnsVGY8Ge6v3itPfCicb+6WJ0XE8ngi S/GvvBcTFNySSYle6eETxUCuEd7FmS8oN4BjerQ1gjdk42axMUQXwui2Xbnl/Sw0nOQH cz74SEyUQV8xaZWEFt/CmJ4djbx8+CikcEv0IUzJ5iyaNyf/sJ5nzlDzt7SW48ZP/zSr 4agg== X-Gm-Message-State: ANoB5pnBPiwqxyhy3hGUCE6yTgJXPUtehY8FoiuROcBsF0ixJesJ2IPW YtiIAhMeSSVHV4tFO4Z7gIjUJLh3dYc= X-Google-Smtp-Source: AA0mqf7GPMBArVH524lq73RzSoAGHb7+RHpfr1jtXL3QAPKJxpLdev8FNRLaVeJOIBXNwBz4KdrJYg== X-Received: by 2002:a05:620a:1a20:b0:6fa:3f2c:c65e with SMTP id bk32-20020a05620a1a2000b006fa3f2cc65emr19923290qkb.326.1669387809820; Fri, 25 Nov 2022 06:50:09 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id w21-20020a05620a445500b006fa4ac86bfbsm2893814qkp.55.2022.11.25.06.50.09 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 25 Nov 2022 06:50:09 -0800 (PST) Content-Type: multipart/alternative; boundary="------------yOcc5lbW46ziDqQxgBSb9zkD" Message-ID: Date: Fri, 25 Nov 2022 08:50:08 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: How to select unique records in PostgreSQL Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------yOcc5lbW46ziDqQxgBSb9zkD Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Honestly, you do it *in PostgreSQL* the same way you do it in all the other SQL RDBMSs. On 11/24/22 06:01, Rama Krishnan wrote: > > Hi All, > > > I want to get the unique wallet_id from this table even it was repeated on > multiple occasions I should calculate only once as well as if the > wallet_id was calculated on previous month it shouldn't be calculate on > next months > > > create table student_id > ( > student_wallet_id int, > card_id int, > created_date date > ) > > insert into student_id values ('w1', 'c1', '2022-04-01') > > > insert into student_id values ('w1', 'c2', '2022-04-03') > > > insert into student_id values ('w1', 'c3', '2022-04-04') > > insert into student_id values ('w1', 'c4', '2022-05-01') > insert into student_id values ('w2', 'c1', '2022-05-01') > insert into student_id values ('w2', 'c2', '2022-05-04') > insert into student_id values ('w3', 'c4', '2022-05-05') > insert into student_id values ('w3', 'c6', '2022-06-03) > insert into student_id values ('w3', 'c7', '2022-06-05') > insert into student_id values ('w3', 'c8', '2022-06-07') > expected output: > ------------------- > > months count_wallet_id > 2022-04      1 > 2022-05     2 > 2022-05     0 > > > Regards > > A.Rama Krishnan -- Angular momentum makes the world go 'round. --------------yOcc5lbW46ziDqQxgBSb9zkD Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Honestly, you do it in PostgreSQL the same way you do it in all the other SQL RDBMSs.

On 11/24/22 06:01, Rama Krishnan wrote:

Hi All,


I want to get the unique wallet_id from this table even it was repeated on multiple occasions I should calculate only once as well as if the wallet_id was calculated on previous month it shouldn't be calculate on next months


create table student_id
(
student_wallet_id int,
card_id int,
created_date date
)

insert into  student_id values ('w1', 'c1', '2022-04-01')


insert into  student_id values ('w1', 'c2', '2022-04-03')


insert into  student_id values ('w1', 'c3', '2022-04-04')

insert into  student_id values ('w1', 'c4', '2022-05-01')
insert into  student_id values ('w2', 'c1', '2022-05-01')
insert into  student_id values ('w2', 'c2', '2022-05-04')
insert into  student_id values ('w3', 'c4', '2022-05-05')
insert into  student_id values ('w3', 'c6', '2022-06-03)
insert into  student_id values ('w3', 'c7', '2022-06-05')
insert into  student_id values ('w3', 'c8', '2022-06-07')
expected output:
-------------------

months      count_wallet_id
2022-04      1
2022-05      2
2022-05      0


Regards

A.Rama Krishnan


--
Angular momentum makes the world go 'round.
--------------yOcc5lbW46ziDqQxgBSb9zkD--