troubleshooting Question

Group By issue

Avatar of futureDBA
futureDBA asked on
Oracle Database
9 Comments1 Solution296 ViewsLast Modified:
in reality I only want to group by cm_custno, but when I add "+TH_TKTOTL" to my first sum statement, i am told its not a valid group by, if i add sdate to the groupby clause then the query works, but its being grouped by date when I only want to group by customer number


SELECT 
       CM_CUSTNO AS "Customer Number",
       CM_CUSTMR_NAME as "Business Name",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE) - (23) AND ROUND(SYSDATE) THEN nvl(th_dolrs,0) END),0)+TH_TKTOTL AS "Current Period",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND((SYSDATE - (23)) - (30)) AND ROUND(SYSDATE - 30) THEN nvl(th_dolrs,0) END),0) AS "Second Period",
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE - ((30)*3)) and ROUND(SYSDATE - ((30)*2)) then th_dolrs END),0) as "Third Period",
       nvl(SUM(CASE WHEN sdate < ROUND(SYSDATE - ((30)*3)) then th_dolrs END),0) as "Fourth Period"
    FROM(   
SELECT 
        CM_CUSTNO,
        THD_TICKNO, 
        TH_PRODNO, 
        TH_UNITS, 
        TH_DOLRS, 
        PM_PRDESC, 
        PM_VOLUME, 
        CM_TKTMSG, 
        CM_CUSTMR_NAME, 
        TH_CSHRCV, 
        TH_PURORD, 
        TH_ROUTNO, 
        TO_DATE((thd_tranda) || '-' || thd_tranmo || '-' || thd_trancn || thd_tranyr, 'DD-MM-YYYY') AS "SDATE", 
        CM_NOWDUE AS "CUSTOMER BALANCE", 
        CM_SLSMAN, 
        THD_CUSTNO, 
        THD_TRANYR, 
        THD_TRANDA, 
        TH_TKTOTL, 
        TH_CSHRCV AS DIFF, 
        cm_chnid
FROM ((THSDTL@"DB2" INNER JOIN CUSMAS@"DB2" ON THSDTL.THD_CUSTNO = CUSMAS.CM_CUSTNO) 
	INNER JOIN RAPFILES.PROMAS@"DB2" ON THSDTL.TH_PRODNO = PROMAS.PM_PRODNO) 
	INNER JOIN TICHIS@"DB2" ON (THSDTL.THD_TICKNO = TICHIS.TH_TICKNO)
  LEFT OUTER JOIN TICKET@"DB2" ON TICKET.TK_CUSTNO = CUSMAS.CM_CUSTNO 
  AND CUSMAS.CM_CUSTNO = TICHIS.TH_CUSTNO
WHERE TH_TKTOTL-TH_CSHRCV >1
AND CM_CUSTNO IN (1606,1608,1609,1611,1614,1620,9701,9778))
GROUP BY
CM_CUSTNO,
CM_CUSTMR_NAME;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros