even referencing the previous question doesn't help here because there is no sample data in that question either
SELECT Branch_No
, MAX ( CASE WHEN Mth = 'Jan' THEN Trans_Amount ELSE 0 END ) Jan
, MAX ( CASE WHEN Mth = 'Jan' THEN Mth_Avg ELSE 0 END ) Jan_Avg
, MAX ( CASE WHEN Mth = 'Feb' THEN Trans_Amount ELSE 0 END ) Feb
, MAX ( CASE WHEN Mth = 'Feb' THEN Mth_Avg ELSE 0 END ) Feb_Avg
, MAX ( CASE WHEN Mth = 'Mar' THEN Trans_Amount ELSE 0 END ) Mar
, MAX ( CASE WHEN Mth = 'Mar' THEN Mth_Avg ELSE 0 END ) Mar_Avg
, MAX ( CASE WHEN Mth = 'Apr' THEN Trans_Amount ELSE 0 END ) Apr
, MAX ( CASE WHEN Mth = 'Apr' THEN Mth_Avg ELSE 0 END ) Apr_Avg
FROM ( SELECT Branch_No
, TO_CHAR ( Trans_Date, 'Mon' ) Mth
, SUM ( Trans_Amount ) Trans_Amount
, SUM ( Trans_Amount )
/ COUNT ( DISTINCT TRUNC ( Trans_Date ) )
Mth_Avg
FROM My_Trans
GROUP BY Branch_No, TO_CHAR ( Trans_Date, 'Mon' ) )
GROUP BY Branch_No
ORDER BY Branch_No;
SELECT Branch_No
, MAX ( CASE WHEN Mth = 'Jan' THEN Trans_Amount ELSE 0 END ) Jan
, MAX ( CASE WHEN Mth = 'Jan' THEN Mth_Avg ELSE 0 END ) Jan_Avg
, MAX ( CASE WHEN Mth = 'Feb' THEN Trans_Amount ELSE 0 END ) Feb
, MAX ( CASE WHEN Mth = 'Feb' THEN Mth_Avg ELSE 0 END ) Feb_Avg
, MAX ( CASE WHEN Mth = 'Mar' THEN Trans_Amount ELSE 0 END ) Mar
, MAX ( CASE WHEN Mth = 'Mar' THEN Mth_Avg ELSE 0 END ) Mar_Avg
, MAX ( CASE WHEN Mth = 'Apr' THEN Trans_Amount ELSE 0 END ) Apr
, MAX ( CASE WHEN Mth = 'Apr' THEN Mth_Avg ELSE 0 END ) Apr_Avg
FROM (
SELECT Branch_No
, TO_CHAR ( Trans_Date, 'Mon' ) Mth
, SUM ( Trans_Amount ) Trans_Amount
, SUM ( Trans_Amount )
/ COUNT ( DISTINCT TRUNC ( Trans_Date ) ) Mth_Avg
FROM My_Trans
GROUP BY Branch_No, TO_CHAR ( Trans_Date, 'Mon' )
)
GROUP BY Branch_No
ORDER BY Branch_No
BRANCH_NO JAN JAN_AVG FEB FEB_AVG MAR MAR_AVG APR APR_AVG
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0 0 0 0 1000 200 0 0
2 0 0 0 0 1400 280 0 0
3 0 0 0 0 1700 283.3333 0 0
4 0 0 0 0 1700 212.5 0 0
4 rows selected.
:p
I don't understand your expected results.
for week 1 of branch 1 you have only 2 in your expected results, but I see 4 transactions for branch 1
Week 1 (01->07 Mar Week 2 (08->14 Mar
Branch-No Sum Amt Trans_Count Week Avg Sum Amt Trans_Count Week Avg
1 400 2 200 600 3 200
2 400 2 200 1000 3 333.3333333
3 500 3 166.6666667 1200 3 400
4 900 5 180 800 3 266.6666667
BRANCH_NO WK1_SUM_AMT WK1_CNT WK1_AVG_AMT WK2_SUM_AMT WK2_CNT WK2_AVG_AMT
1 400 2 100 600 3 200
2 400 2 100 1000 3 200
3 500 3 100 1200 3 200
4 900 5 100 800 3 200
SELECT unit,
SUM(DECODE(wk, 40, trans_amount)) wk1_sum_amt,
COUNT(DISTINCT (DECODE(wk, 40, dy))) wk1_cnt,
AVG(DECODE(wk, 40, trans_amount)) wk1_avg_amt,
SUM(DECODE(wk, 41, trans_amount)) wk2_sum_amt,
COUNT(DISTINCT (DECODE(wk, 41, dy))) wk2_cnt,
AVG(DECODE(wk, 41, trans_amount)) wk2_avg_amt,
SUM(DECODE(wk, 42, trans_amount)) wk3_sum_amt,
COUNT(DISTINCT (DECODE(wk, 42, dy))) wk3_cnt,
AVG(DECODE(wk, 42, trans_amount)) wk3_avg_amt,
SUM(DECODE(wk, 43, trans_amount)) wk4_sum_amt,
COUNT(DISTINCT (DECODE(wk, 43, dy))) wk4_cnt,
AVG(DECODE(wk, 43, trans_amount)) wk4_avg_amt,
SUM(DECODE(wk, 44, trans_amount)) wk5_sum_amt,
COUNT(DISTINCT (DECODE(wk, 44, dy))) wk5_cnt,
AVG(DECODE(wk, 44, trans_amount)) wk5_avg_amt,
SUM(DECODE(wk, 44, trans_amount)) wk6_sum_amt,
COUNT(DISTINCT (DECODE(wk, 44, dy))) wk6_cnt,
AVG(DECODE(wk, 44, trans_amount)) wk6_avg_amt,
SUM(DECODE(wk, 45, trans_amount)) wk7_sum_amt,
COUNT(DISTINCT (DECODE(wk, 45, dy))) wk7_cnt,
AVG(DECODE(wk, 45, trans_amount)) wk7_avg_amt,
SUM(DECODE(wk, 46, trans_amount)) wk8_sum_amt,
COUNT(DISTINCT (DECODE(wk, 46, dy))) wk8_cnt,
AVG(DECODE(wk, 46, trans_amount)) wk8_avg_amt
FROM (SELECT unit,
( quantity * price ) trans_amount,
TRUNC(dob) dy,
CEIL(EXTRACT(DAY FROM dob) / 7) wk
FROM cr_gnditem t where unit = '1270')
GROUP BY unit
it really result no data , while in fact it has
no where in either of Mike's posts does he reference the week number of the year
and, his results are based on months, where as the expected results you requested for for weeks.
CEIL(EXTRACT(DAY FROM trans_date) / 7) wk
by this one to_number(to_char(trans_date,'IW'))
to get week number of the year
Title | # Comments | Views | Activity |
---|---|---|---|
Oracle 12c SQL - Finding a specific string in a long data type column | 4 | 49 | |
Oracle Finace | 3 | 43 | |
Export BLOB data from Oracle 10g | 4 | 23 | |
SQL Query - Oracle 10g - Subract date from next data row | 4 | 36 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
8 Experts available now in Live!