select Statement with CASE and month count [message #674720] |
Tue, 12 February 2019 08:33 |
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 |
|
rrcr
Messages: 18 Registered: January 2019
|
Junior Member |
|
|
evoradba wrote on Tue, 12 February 2019 08:33Hello
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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
evoradba wrote on Tue, 12 February 2019 06:33Hello
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 |
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
|
|
|
|
|
|