Avatar of futureDBA
futureDBA
 asked on

Group By issue

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;

Open in new window

Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

Add TH_TKTOTL to the group by.
futureDBA

ASKER
when i do that it splits cm_custno, so for customer 1609 i have multiple rows returned in the query
slightwv (䄆 Netminder)

Then try another outer select:


SELECT
       "Customer Number",
       "Business Name",
       "Current Period" +TH_TKTOTL AS "Current Period",
       "Second Period",
       "Third Period",
       "Fourth Period"
    FROM(
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) 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(
...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
futureDBA

ASKER
that makes sense.  give me a sec, let me try that
futureDBA

ASKER
ORA-00904: "TH_TKTOTL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 4 Column: 5
futureDBA

ASKER
SELECT
  CM_CUSTNO AS "Customer Number",
  BusNam AS "Business Name",
  CP+TH_TKTOTL AS "Current Priod",
  SP AS "Second Period",
  TP AS "Third Period",
  FP AS "Fourth Period"
FROM(
SELECT 
       CM_CUSTNO,
       CM_CUSTMR_NAME as BusNam,
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE) - (23) AND ROUND(SYSDATE) THEN nvl(th_dolrs,0) END),0) AS CP,
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND((SYSDATE - (23)) - (30)) AND ROUND(SYSDATE - 30) THEN nvl(th_dolrs,0) END),0) AS SP,
       nvl(SUM(CASE WHEN sdate BETWEEN ROUND(SYSDATE - ((30)*3)) and ROUND(SYSDATE - ((30)*2)) then th_dolrs END),0) as TP,
       nvl(SUM(CASE WHEN sdate < ROUND(SYSDATE - ((30)*3)) then th_dolrs END),0) as FP
    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);//

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>ORA-00904: "TH_TKTOTL": invalid identifier

DUH... yes, it needs to be part of the innser select which leads back to it having to be part of the group by.

try adding TH_TKTOTL to the group by then the SUM in the outer query.
futureDBA

ASKER
:-\ sorry, your last comment did not make sense to me. kinda lost me


th_tktotl is on the first select does it need to be in the middle one ?
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question