POSTGRESQL GROUP BY QUERY-CodePudding

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

This is what I have.

And I want it like this

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

So that I have unique dates. Thanks in advance. I tried some solutions with "JOINS" but none of them worked. I hope that you have any ideas...

CodePudding user response：

Could you please clear your question?

CodePudding user response：

You can use the DISTINCT keyword to return only unique values in the "DATUM" column.

Here is the modified query:

   SELECT
    DISTINCT 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
    "MODIFIED_STATUS"
ORDER BY
    "DATUM" ASC