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 1pLVp6-0003Xs-Mu for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 20:59:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLVp5-0004Y3-6Y for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 20:59:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLVp4-0004Vy-QB for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 20:59:46 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLVp2-0003Q8-AB for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 20:59:46 +0000 Received: by mail-pl1-x636.google.com with SMTP id k13so6273775plg.0 for ; Fri, 27 Jan 2023 12:59:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=peqehVx9f2JD6XbjvK4JvZ3AfJ9GbXYQV4Mo1ozMjfs=; b=QOyVu7TLZkMY2OmqiBy8rm9QC3HPf1qqsU2z/3UBaijqZkcFmFzklViV2Wsz6hdIQh C3Im+UOEd/xqYlQW9fISLCZC/uQUW58doygEKqA9dk3YkRmRsEHmSzg+m68mFpPQ+iO4 vPI4dqL63yBN0bAhsgZ/IvhyKR7qYozgcIUv+TOAy4TbA2S08nPBq75Iktl4tCTeRovy 4fQ8oJH3BOyU2pbnO1lDF7mQ2gW+O/EQdD9gCxjd5qSGQMQmgVtlO4jUFwccheQKYe1c 3qumrW3s6ZxKD/arbtAR77N+CsV28xtAbTrc2WHfiUFSb9H6Gs7Jq+Oi4gojWmz+ThIU dQWg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=peqehVx9f2JD6XbjvK4JvZ3AfJ9GbXYQV4Mo1ozMjfs=; b=RgKjATfcK7J2NCpCVGmZeMr/m2BzKhZ0pz8VRPmV+R+rITmUeplHCO8sgRTxnfWmZ0 KW374coAsqCE5CXlnL9jC4CyK7ZcLj0ATRo3jujWPzCXp2YO8HGeRlAqQ9/hlU/K8NXn kxnogN9A7Ziky39TS6PvOUl9sdPnzX15EQAUJIbgTX+vtYURMvNUcM9+fTYbKPAYwASF UmjDeLTXQajuHl9hUOL2WFtiBpGmrjNfpkA7Pl6s9JehIKXv3iDoMFt8O/aaZu1ah0Lf kzIvg9F+19xHKdnzjANNuIEhM82MCYbiVRDfgIxVJvYiaY95Z9SNmsZzZkvJKBVAH4Hb 56tw== X-Gm-Message-State: AFqh2kqgXb7j2EdVSDE5jkMUAJTEWjCgflwrlvnQnWnuIME1zFoztX6y F3bAEdjucfQvOp0s8GGz/SR4FeQrKyI= X-Google-Smtp-Source: AMrXdXsoNhOAl1KS1R5t9R4Mv+yMaAqZ7QpMEWYVeIh+VdMTOU+qZX86vwgYKHI1DYq736kAO48LAQ== X-Received: by 2002:a17:902:ea0e:b0:195:f0f9:a9fb with SMTP id s14-20020a170902ea0e00b00195f0f9a9fbmr33920871plg.11.1674853181445; Fri, 27 Jan 2023 12:59:41 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:fea0:6247:5eca:cde2? ([2601:681:5a00:f790:fea0:6247:5eca:cde2]) by smtp.gmail.com with ESMTPSA id 21-20020a170902c21500b001947ba0ac8fsm3269959pll.236.2023.01.27.12.59.40 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 27 Jan 2023 12:59:40 -0800 (PST) Content-Type: multipart/alternative; boundary="------------DMIfucqWTMDUdbn7N1JAEd2v" Message-ID: <71aa4ae1-f789-485a-ae58-2acec9aa2df0@gmail.com> Date: Fri, 27 Jan 2023 13:59:39 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Content-Language: en-CA To: "pgsql-generallists.postgresql.org" From: Rob Sargent Subject: nextval per counted 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. --------------DMIfucqWTMDUdbn7N1JAEd2v Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit I'm trying to craft SQL to invoke a sequence nextval once per grouped value. So far I have this: with husb as( select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as mates from emp_all_by3 e group by e.ma order by mates ) select mates, count(*) from husb group by mates order by mates desc; which works nicely but it "ids" each null separately. The following lets me count the "fixes" as a mate with husb as(   select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) mates   from emp_all_by3 e   where ma is not null   group by e.ma order by mates ) select mates, count(*) from husb group by mates order by mates desc; with husb as(   select e.ma, coalesce(e.pa,'fix') as pa   from emp_all_by3 e   where e.ma is not null ), fixed as (   select e.ma, count(distinct e.pa) mates   from husb e group by e.ma order by mates ) select mates, count(*) from fixed group by mates order by mates desc; but I would love to able to assign a single "nextval"  to those fixes. Any pointers appreciated. --------------DMIfucqWTMDUdbn7N1JAEd2v Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit I'm trying to craft SQL to invoke a sequence nextval once per grouped value.

So far I have this:

with husb as(
select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as mates
from emp_all_by3 e group by e.ma order by mates
)
select mates, count(*)
from husb
group by mates order by mates desc;

which works nicely but it "ids" each null separately.

The following lets me count the "fixes" as a mate
with husb as(
  select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) mates
  from emp_all_by3 e
  where ma is not null
  group by e.ma order by mates
)
select mates, count(*) from husb group by mates order by mates desc;

with husb as(
  select e.ma, coalesce(e.pa,'fix') as pa
  from emp_all_by3 e
  where e.ma is not null
),
fixed as (
  select e.ma, count(distinct e.pa) mates
  from husb e group by e.ma order by mates
)
select mates, count(*) from fixed group by mates order by mates desc;

but I would love to able to assign a single "nextval"  to those fixes.

Any pointers appreciated.


--------------DMIfucqWTMDUdbn7N1JAEd2v--