May 17, 2019 at 1:05 pm
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!
May 17, 2019 at 1:10 pm
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
May 17, 2019 at 1:23 pm
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
May 17, 2019 at 1:40 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply