Home » SQL & PL/SQL » SQL & PL/SQL » select Statement with CASE and month count (rdbms 12c )
select Statement with CASE and month count [message #674720] Tue, 12 February 2019 08:33 Go to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
Hello
I'm trying to gather per month counts with CASE and I'm stuck
this is my query, I'm need a per month count
HELP
SELECT (
CASE
WHEN SUMMARY_FUNC = 'SUM'
THEN SUM(TRADE_COUNT)
WHEN SUMMARY_FUNC = 'AVG'
THEN AVG(TRADE_COUNT)
WHEN SUMMARY_FUNC = 'MIN'
THEN MIN(TRADE_COUNT)
WHEN SUMMARY_FUNC = 'MAX'
THEN MAX(TRADE_COUNT)
ELSE SUM(TRADE_COUNT)
END) AS TRADE_COUNT,
A.PRODUCT_ID,
A.ORDER_MONTH,
A.ORDER_YEAR
FROM DAILY_TRADE_COUNTS A,
DAILY_TRADE_COUNT_LIMITS B,
PRODUCT C
WHERE A.PRODUCT_ID = C.PRODUCT_ID
AND A.PRODUCT_ID = B.PRODUCT_ID (+)
AND A.RECORD_TYPE = B.RECORD_TYPE (+)
GROUP BY A.PRODUCT_ID,
A.ORDER_MONTH,
A.ORDER_YEAR,
A.TRADE_COUNT,
A.RECORD_TYPE,
SUMMARY_FUNC
ORDER BY
A.ORDER_YEAR,A.ORDER_MONTH, A.PRODUCT_ID ;

Re: select Statement with CASE and month count [message #674723 is a reply to message #674720] Tue, 12 February 2019 08:50 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
evoradba wrote on Tue, 12 February 2019 08:33
Hello
I'm trying to gather per month counts with CASE and I'm stuck
this is my query, I'm need a per month count
HELP
SELECT (
CASE
WHEN SUMMARY_FUNC = 'SUM'
THEN SUM(TRADE_COUNT)
WHEN SUMMARY_FUNC = 'AVG'
THEN AVG(TRADE_COUNT)
WHEN SUMMARY_FUNC = 'MIN'
THEN MIN(TRADE_COUNT)
WHEN SUMMARY_FUNC = 'MAX'
THEN MAX(TRADE_COUNT)
ELSE SUM(TRADE_COUNT)
END) AS TRADE_COUNT,
A.PRODUCT_ID,
A.ORDER_MONTH,
A.ORDER_YEAR
FROM DAILY_TRADE_COUNTS A,
DAILY_TRADE_COUNT_LIMITS B,
PRODUCT C
WHERE A.PRODUCT_ID = C.PRODUCT_ID
AND A.PRODUCT_ID = B.PRODUCT_ID (+)
AND A.RECORD_TYPE = B.RECORD_TYPE (+)
GROUP BY A.PRODUCT_ID,
A.ORDER_MONTH,
A.ORDER_YEAR,
A.TRADE_COUNT,
A.RECORD_TYPE,
SUMMARY_FUNC
ORDER BY
A.ORDER_YEAR,A.ORDER_MONTH, A.PRODUCT_ID ;

SELECT 
SUM(CASE WHEN SUMMARY_FUNC = 'SUM' THEN TRADE_COUNT ELSE 0 END) AS MONTH_SUM,
AVG(CASE WHEN SUMMARY_FUNC = 'AVG' THEN TRADE_COUNT ELSE 0 END) AS MONTH_AVG,
MIN(CASE WHEN SUMMARY_FUNC = 'MIN' THEN TRADE_COUNT ELSE 0 END) AS MONTH_MIN,
MAX(CASE WHEN SUMMARY_FUNC = 'MAX' THEN TRADE_COUNT ELSE 0 END) AS MONTH_MAX,
A.ORDER_MONTH
	
FROM DAILY_TRADE_COUNTS A
	,DAILY_TRADE_COUNT_LIMITS B
	,PRODUCT C
WHERE A.PRODUCT_ID = C.PRODUCT_ID
	AND A.PRODUCT_ID = B.PRODUCT_ID(+)
	AND A.RECORD_TYPE = B.RECORD_TYPE(+)
GROUP BY ,A.ORDER_MONTH
	
ORDER BY A.ORDER_MONTH
	
Re: select Statement with CASE and month count [message #674725 is a reply to message #674720] Tue, 12 February 2019 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
evoradba wrote on Tue, 12 February 2019 06:33
Hello
I'm trying to gather per month counts with CASE and I'm stuck
this is my query, I'm need a per month count
HELP
SELECT (
  CASE
    WHEN SUMMARY_FUNC = 'SUM'
    THEN SUM(TRADE_COUNT)
    WHEN SUMMARY_FUNC = 'AVG'
    THEN AVG(TRADE_COUNT)
    WHEN SUMMARY_FUNC = 'MIN'
    THEN MIN(TRADE_COUNT)
    WHEN SUMMARY_FUNC = 'MAX'
    THEN MAX(TRADE_COUNT)
    ELSE SUM(TRADE_COUNT) 
  END)             AS TRADE_COUNT,
  A.PRODUCT_ID,
  A.ORDER_MONTH,
  A.ORDER_YEAR
FROM DAILY_TRADE_COUNTS A,
  DAILY_TRADE_COUNT_LIMITS B,
  PRODUCT C
WHERE A.PRODUCT_ID = C.PRODUCT_ID
AND A.PRODUCT_ID   = B.PRODUCT_ID (+)
AND A.RECORD_TYPE  = B.RECORD_TYPE (+)
GROUP BY A.PRODUCT_ID,
A.ORDER_MONTH,
A.ORDER_YEAR,
A.TRADE_COUNT,
  A.RECORD_TYPE,
  SUMMARY_FUNC
ORDER BY 
  A.ORDER_YEAR,A.ORDER_MONTH, A.PRODUCT_ID ;
 
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We don't have your tables.
We don't have your data.
We don't have your requirements.

We don't know what your expected or desired results should be.

You give us a mystery but provide us no clues to solve it.
Re: select Statement with CASE and month count [message #674728 is a reply to message #674725] Tue, 12 February 2019 14:04 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
thank you so much this definitely helped THANK YOU
however
is there away to tacking even more , to do a count per month and display all counts per month per PRO
TRADE_COUNT PRO ORD ORDER_YEAR                                                  
----------- --- --- ----------                                                  
          2 AA  NOV       2001                                                  
          3 AA  NOV       2001                                                  
         72 AA  NOV       2001  
         78 BB  OCT       2011                                                  
         10 BB  OCT       2011 
SOMETHING like this
  TRADE_COUNT PRO ORD ORDER_YEAR                                                  
----------- --- --- ----------                                                  
          77  AA  NOV       2001 
          88  BB  OCT       2011 
*Blackswan added {code} tags. PLEASE do so yourself in the future!

[Updated on: Tue, 12 February 2019 14:45] by Moderator

Report message to a moderator

Re: select Statement with CASE and month count [message #674730 is a reply to message #674728] Tue, 12 February 2019 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to stop changing the requirements!
First you said you wanted totals per month.
Above shows total per year.
Yes, yearly total is possible by using different GROUP BY clause.
Re: select Statement with CASE and month count [message #674731 is a reply to message #674730] Tue, 12 February 2019 15:23 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
thank you - sorry if I caused confusion
Re: select Statement with CASE and month count [message #674732 is a reply to message #674731] Tue, 12 February 2019 17:32 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
When do you plan to start following the Posting Guidelines?
Previous Topic: Difference between sum(decode) and count
Next Topic: Add new columns and update to 600MB table online mode with zero downtime
Goto Forum:
  


Current Time: Thu Mar 28 07:15:44 CDT 2024