PostGRESQL按查询分组

hyrbngr7 于 2023-01-25 发布在 PostgreSQL

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

SELECT
    concat(
        EXTRACT(
            YEAR
            FROM
                "BEAUFTRAGUNG_DATUM"
        ),
        '-',
        TO_CHAR(
            EXTRACT(
                MONTH
                FROM
                    "BEAUFTRAGUNG_DATUM"
            ),
            'fm00'
        )
    ) AS "DATUM",
    CASE
        WHEN (
            "STATUS" in (
                '....', '...'
            )
        ) THEN 'OTHER'
        WHEN ("STATUS" = 'BESTELLT') THEN 'BESTELLT'
        WHEN ("STATUS" = 'VOR_PRODUKTION') THEN 'VOR_PRODUKTION'
    END AS "MODIFIED_STATUS",
    COUNT(*) AS "ANZAHL"
FROM
    PUBLIC."TXS"
WHERE
    "FLAG_POS" = '1'
GROUP BY
    "DATUM",
    "MODIFIED_STATUS"
ORDER BY
    "DATUM" ASC

这是我的全部
我想要这样

DATUM       OTHER     BESTELLT       VOR_PRODUKTION
2021-11     47        87             366
2022-01     1         0              0
2022-02     82        73             356

所以我有独特的日期。提前感谢。我尝试了一些解决方案与“加入”，但没有一个工作。我希望你有任何想法...

postgresql

来源：https://stackoverflow.com/questions/75170587/postgresql-group-by-query

1条答案

按热度按时间

carvr3hs1#

此查询应该能够使用给定的信息获得所需的结果：

SELECT
    txs.DATUM,
    COALESCE(other.sum, 0) AS other,
    COALESCE(bestellt.sum, 0) AS bestellt,
    COALESCE(vor_produktion.sum, 0) AS vor_produktion
FROM
    PUBLIC."TXS" txs
LEFT JOIN (SELECT tsx2.DATUM, SUM(tsx2.ANZAHL) AS sum FROM PUBLIC."TXS" tsx2 WHERE tsx2.MODIFIED_STATUS = 'OTHER' GROUP BY tsx2.DATUM) other ON other.DATUM = txs.DATUM
LEFT JOIN (SELECT tsx2.DATUM, SUM(tsx2.ANZAHL) AS sum FROM PUBLIC."TXS" tsx2 WHERE tsx2.MODIFIED_STATUS = 'BESTELLT' GROUP BY tsx2.DATUM) bestellt ON bestellt.DATUM = txs.DATUM
LEFT JOIN (SELECT tsx2.DATUM, SUM(tsx2.ANZAHL) AS sum FROM PUBLIC."TXS" tsx2 WHERE tsx2.MODIFIED_STATUS = 'VOR_PRODUKTION' GROUP BY tsx2.DATUM) vor_produktion ON vor_produktion.DATUM = txs.DATUM
GROUP BY txs.DATUM, other.sum, bestellt.sum, vor_produktion.sum

这里，我们使用子查询连接3列，并将<null>值替换为0

赞(0）回复(0）举报 2023-01-25

我来回答

PostGRESQL按查询分组

1条答案

相关问题

热门标签

最新问答