postgresql group by具有2个条件总计和过期计数(以postgres表示)

hc2pp10m 于 2022-11-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(146)

我需要按taluk名称和taluk_code分组的总数和到期日期。请帮助我按taluk名称和taluk_code分组。总数和到期日期：

SELECT
    l.taluk_code, t.taluk_name,
    count (serial_number) as Total
FROM
    license_register l, taluk t where l.taluk_code=t.taluk_code and rev_district_code='17' and 
    l.validity_to <= now()
  group by l.taluk_code, t.taluk_name

无失效日期

SELECT
    l.taluk_code, t.taluk_name,
    count (serial_number) as Total
FROM
    license_register l, taluk t where l.taluk_code=t.taluk_code and rev_district_code='17' 
  group by l.taluk_code, t.taluk_name

请合并并给予解决方案

"taluk_code"    "taluk_name"    "total"
"01 "   "Ariyalur"  2
"04 "   "Sendurai"  1

"taluk_code"    "taluk_name"    "ExpiryCount"
"01 "   "Ariyalur"  2
"04 "   "Sendurai"  1

我需要以下输出：

"taluk_code"    "taluk_name"    "total" "ExpiryCount"
"01 "   "Ariyalur"  2 1
"04 "   "sendurai"  1 4

postgresql

来源：https://stackoverflow.com/questions/74597837/group-by-with-2-conditions-total-and-expiry-count-in-postgres

1条答案

按热度按时间

kmpatx3s1#

SELECT
    l.taluk_code,
    t.taluk_name,
    count(serial_number) AS total,
    count(serial_number) FILTER (WHERE l.validity_to <= now()) AS expiry_count
FROM
    license_register l,
    taluk t
WHERE
    l.taluk_code = t.taluk_code
    AND rev_district_code = '17'
GROUP BY
    l.taluk_code,
    t.taluk_name;

过滤器教程：https://modern-sql.com/feature/filter#footnote-0

赞(0）回复(0）举报 2022-11-29

我来回答

postgresql group by具有2个条件总计和过期计数(以postgres表示)

1条答案

相关问题

热门标签

最新问答