Group By issue

futureDBA
futureDBA used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Add TH_TKTOTL to the group by.

Author

Commented:
when i do that it splits cm_custno, so for customer 1609 i have multiple rows returned in the query
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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(
...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
that makes sense.  give me a sec, let me try that

Author

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

Author

Commented:
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

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
:-\ 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 ?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>th_tktotl is on the first select does it need to be in the middle one ?

Yes.  Think about it.  How can you 'select' a value that is not coming from the results?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial