SQL question about grouping

Question

Post reply

SQL question about grouping

louislsh

Old Hand

Points: 358
More actions
May 17, 2019 at 1:05 pm

#3644536

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.

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

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

louislsh Old Hand Points: 358 More actions · Answer 1

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: 18400 More actions · Answer 2

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 Old Hand Points: 358 More actions · Answer 3

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! =)

SQL question about grouping

Attachments:

Cookies on SQLServerCentral