SQL question about grouping

  • louislsh

    SSC Journeyman

    Points: 75

    Hi, i am a newbie in SQL and today i hit an issue where the data can't group.

    I know im doing it wrong but i have not much idea how i can get past date and type fields.

    I'm trying to group by CUSTID, each CUSTID in a single row

    Attaching the both files, hope someone can guide me. Thank you very much!

    Attachments:
    You must be logged in to view attached files.
  • louislsh

    SSC Journeyman

    Points: 75

    select

    CT.LINTCUSTOMERNUM as CUSTID,

    SUM(ACD.CURAMOUNT) as [TOTAL AMOUNT],

    CASE when ACD.DTMTRANS < '2018-12-31' and ACD.STRTYPE='SALE' then SUM(ACD.CURDEBIT) Else '0' END as EARNED,

    CASE when ACD.DTMTRANS < '2018-12-31' and ACD.STRTYPE='PAY' then SUM(ACD.CURCREDIT) Else '0' END as REDEEM,

    CASE when ACD.DTMTRANS > '2018-12-31' and ACD.STRTYPE='SALE' then SUM(ACD.CURDEBIT) Else '0' END as [2019 EARNED],

    CASE when ACD.DTMTRANS > '2018-12-31' and ACD.STRTYPE='PAY' then SUM(ACD.CURCREDIT) Else '0' END as [2019 REDEEM]

    from ACDTL ACD

    join ACHDR as ACH on ACH.LINTACCOUNTCODE=ACD.LINTACCOUNTCODE

    join CUSTOMER as CT on CT.LINTCUSTOMERNUM=ACH.STRACCOUNTCODE

    Group by CT.LINTCUSTOMERNUM,ACD.STRTYPE,ACD.DTMTRANS

    order by CT.LINTCUSTOMERNUM

  • Jonathan AC Roberts

    SSCoach

    Points: 16690

    You have SUM in the wrong place.

    Also, should not be ELSE '0' but ELSE 0 or ELSE NULL

    Also, only need to group by CT.LINTCUSTOMERNUM

    SELECT CT.LINTCUSTOMERNUM AS CUSTID,
    SUM(ACD.CURAMOUNT) AS [TOTAL AMOUNT],
    SUM(CASE
    WHEN ACD.DTMTRANS < '20181231'
    AND ACD.STRTYPE = 'SALE'
    THEN ACD.CURDEBIT
    ELSE NULL
    END) AS EARNED,
    SUM(CASE
    WHEN ACD.DTMTRANS < '20181231'
    AND ACD.STRTYPE = 'PAY'
    THEN ACD.CURCREDIT
    ELSE NULL
    END) AS REDEEM,
    SUM(CASE
    WHEN ACD.DTMTRANS > '20181231'
    AND ACD.STRTYPE = 'SALE'
    THEN ACD.CURDEBIT
    ELSE NULL
    END) AS [2019 EARNED],
    SUM(CASE
    WHEN ACD.DTMTRANS > '20181231'
    AND ACD.STRTYPE = 'PAY'
    THEN ACD.CURCREDIT
    ELSE NULL
    END) AS [2019 REDEEM]
    FROM ACDTL ACD
    JOIN ACHDR AS ACH
    ON ACH.LINTACCOUNTCODE = ACD.LINTACCOUNTCODE
    JOIN CUSTOMER AS CT
    ON CT.LINTCUSTOMERNUM = ACH.STRACCOUNTCODE
    GROUP BY CT.LINTCUSTOMERNUM
    ORDER BY CT.LINTCUSTOMERNUM
  • louislsh

    SSC Journeyman

    Points: 75

    Thank you Jonathan, it works like a charm.

    Am very greatful and thanks for sharing your knowledge.

    I'll learn from this and hopefully can give back to the community as much as i could.

    Have a great day! =)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply