Avatar of NiceMan331
NiceMan331
 asked on

count transactions within a query

hi
reference to this question
https://www.experts-exchange.com/Database/Oracle/Q_28553461.html

i feel shame to add another requirement to same question , so i opened a new topic
the sql of slightw will retrieve the sum of transactions amount within a month or a week
ok , now let do more description
in every day , there are many transactions for each store
here i got for example : sum of Mar for store # 01 = 2500 $
and that store works only 25 days , it means no data in 6 days from Mar
so the daily average for that store will be : 2500 / 25  ,  not 2500/31
how i  get the count of transaction per store group by DOB , to get the daily average per store in the same query
Oracle Database

Avatar of undefined
Last Comment
NiceMan331

8/22/2022 - Mon
Sean Stuber

please post sample data and expected output
even referencing the previous question doesn't help here because there is no sample data in that question either
NiceMan331

ASKER
Ok , give me 10 minuts plz
MikeOM_DBA

Just count the days, something like:

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;

Open in new window

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
NiceMan331

ASKER
sbsdutbar
here are the sample
look sheet input
then see the result sheet
i first sort the data to explain how to count number of days per specific week
then easy to get the average per week
Mike
please continue the code , what to put inside the brackets after word over
sample.xlsx
Sean Stuber

>> sbsdutbar ?

:)
NiceMan331

ASKER
Sorry , by mistake
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
MikeOM_DBA

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
Sean Stuber

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

also your weekly average for branch 1, week 1 is 200, but every transaction for that branch in that week is 100, so the average has to be 100.
Sean Stuber

Assuming my math is right and the expected results are wrong, then try this...

  SELECT branch_no,
         SUM(DECODE(wk, 1, trans_amount)) wk1_sum_amt,
         COUNT(DECODE(wk, 1, 1)) wk1_cnt,
         AVG(DECODE(wk, 1, trans_amount)) wk1_avg_amt,
         SUM(DECODE(wk, 2, trans_amount)) wk2_sum_amt,
         COUNT(DECODE(wk, 2, 1)) wk2_cnt,
         AVG(DECODE(wk, 2, trans_amount)) wk2_avg_amt,
         SUM(DECODE(wk, 3, trans_amount)) wk3_sum_amt,
         COUNT(DECODE(wk, 3, 1)) wk3_cnt,
         AVG(DECODE(wk, 3, trans_amount)) wk3_avg_amt,
         SUM(DECODE(wk, 4, trans_amount)) wk4_sum_amt,
         COUNT(DECODE(wk, 4, 1)) wk4_cnt,
         AVG(DECODE(wk, 4, trans_amount)) wk4_avg_amt,
         SUM(DECODE(wk, 5, trans_amount)) wk5_sum_amt,
         COUNT(DECODE(wk, 5, 1)) wk5_cnt,
         AVG(DECODE(wk, 5, trans_amount)) wk5_avg_amt
    FROM (SELECT branch_no, trans_amount, CEIL(EXTRACT(DAY FROM trans_date) / 7) wk
            FROM yourtable t)
GROUP BY branch_no
ORDER BY branch_no
NiceMan331

ASKER
mike
your code is correct , but could you please just adjust it to exclude data where trans_amount < 100 $
because i expect the normal sum(trans_amount) per day per store should not be less than 100 , if  true , it means something wrong with the data itself , not in the code , the solution to just avoid it because the count of days here will be bigger than the accurate then the average will be less
Your help has saved me hundreds of hours of internet surfing.
fblack61
NiceMan331

ASKER
i mean :
if branch no 02  in march has sum of 1000 $ , and actual days has data are 17 , one of them has sum(trans_amount) = 60 $
so , i need the days count to be 16 , not 17
thanx
NiceMan331

ASKER
sdstubar
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

yes , you see 4 transactions , but it is related to 2 days only
we are summarizing data group by day
so , number of days having data for week 1 , branch 1 = 2
Sean Stuber

ok, thanks for the explanation of the the trans_count.  I've corrected the query below.
I still don't understand how you got your weekly averages though


  SELECT branch_no,
         SUM(DECODE(wk, 1, trans_amount)) wk1_sum_amt,
         COUNT(DISTINCT (DECODE(wk, 1, dy))) wk1_cnt,
         AVG(DECODE(wk, 1, trans_amount)) wk1_avg_amt,
         SUM(DECODE(wk, 2, trans_amount)) wk2_sum_amt,
         COUNT(DISTINCT (DECODE(wk, 2, dy))) wk2_cnt,
         AVG(DECODE(wk, 2, trans_amount)) wk2_avg_amt,
         SUM(DECODE(wk, 3, trans_amount)) wk3_sum_amt,
         COUNT(DISTINCT (DECODE(wk, 3, dy))) wk3_cnt,
         AVG(DECODE(wk, 3, trans_amount)) wk3_avg_amt,
         SUM(DECODE(wk, 4, trans_amount)) wk4_sum_amt,
         COUNT(DISTINCT (DECODE(wk, 4, dy))) wk4_cnt,
         AVG(DECODE(wk, 4, trans_amount)) wk4_avg_amt,
         SUM(DECODE(wk, 5, trans_amount)) wk5_sum_amt,
         COUNT(DISTINCT (DECODE(wk, 5, dy))) wk5_cnt,
         AVG(DECODE(wk, 5, trans_amount)) wk5_avg_amt
    FROM (SELECT branch_no,
                 trans_amount,
                 TRUNC(trans_date) dy,
                 CEIL(EXTRACT(DAY FROM trans_date) / 7) wk
            FROM yourtable t)
GROUP BY branch_no
ORDER BY branch_no
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NiceMan331

ASKER
it gives no result
i think cause you consider day of the week
while we need week number of the year , as mike he correctly did the code and result correct
only if could execlud the days which its sum of trans_amount less that 100 , it will be perfect
Sean Stuber

>> it gives no result

that seems hard to believe.
there is no where clause on my query, so it must bring back everything in the table.
I might be formatting it wrong, but if there is data it must return "something"


>> i think cause you consider day of the week

actually no, I'm not considering day of the week.  Nothing in your question indicates day matters.


>> while we need week number of the year , as mike he correctly did the code

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.

have you changed your mind about what you want?  because the queries you are saying are correct don't produce results that look anything at all like what you posted in your spreadsheet

specifically I mean these expected results you posted above

            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

Open in new window


which my query almost exactly matches with

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

Open in new window


The only difference being the weekly averages, but given the data you posted, the weekly averages don't make sense in your expected output.

Please explain how the other queries which are based on months, not weeks, are somehow closer to your expected results?
NiceMan331

ASKER
i adjust your code like this : ( considering :
1-  i select one unit only to fast the result
2- i select last 6 weeks of the year ( between 40 and 46 )

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

Open in new window

it really result no data , while in fact it has
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
NiceMan331

ASKER
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.

i'm sorry , i confused between the 2 topic , slightwv is the one who did regarding week number in the other topic
NiceMan331

ASKER
oh , i think you edit your post
actually you have math mistake in your formula
the average calculation is  sum(amount) divided by count of days
in your result , look for the last row :
avg :  900/5 = 180
avg : 800 / 3 = 266
Sean Stuber

ok, so you don't want the average for the week, but a daily average within that week
try this

 SELECT branch_no,
         wk1_sum_amt,
         wk1_cnt,
         wk1_sum_amt / wk1_cnt wk1_avg_amt,
         wk2_sum_amt,
         wk2_cnt,
         wk2_sum_amt / wk2_cnt wk2_avg_amt,
         wk3_sum_amt,
         wk3_cnt,
         wk3_sum_amt / wk3_cnt wk3_avg_amt,
         wk4_sum_amt,
         wk4_cnt,
         wk4_sum_amt / wk4_cnt wk4_avg_amt,
         wk5_sum_amt,
         wk5_cnt,
         wk5_sum_amt / wk5_cnt wk5_avg_amt
    FROM (  SELECT branch_no,
                   SUM(DECODE(wk, 1, trans_amount)) wk1_sum_amt,
                   COUNT(DISTINCT (DECODE(wk, 1, dy))) wk1_cnt,
                   SUM(DECODE(wk, 2, trans_amount)) wk2_sum_amt,
                   COUNT(DISTINCT (DECODE(wk, 2, dy))) wk2_cnt,
                   SUM(DECODE(wk, 3, trans_amount)) wk3_sum_amt,
                   COUNT(DISTINCT (DECODE(wk, 3, dy))) wk3_cnt,
                   SUM(DECODE(wk, 4, trans_amount)) wk4_sum_amt,
                   COUNT(DISTINCT (DECODE(wk, 4, dy))) wk4_cnt,
                   SUM(DECODE(wk, 5, trans_amount)) wk5_sum_amt,
                   COUNT(DISTINCT (DECODE(wk, 5, dy))) wk5_cnt
              FROM (SELECT branch_no,
                           trans_amount,
                           TRUNC(trans_date) dy,
                           CEIL(EXTRACT(DAY FROM trans_date) / 7) wk
                      FROM yourtable t)
          GROUP BY branch_no)
ORDER BY branch_no
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

I just saw this

>> 2- i select last 6 weeks of the year ( between 40 and 46 )

you can't use the week numbers like that in my query.
my query counts weeks within a month starting at day 1 of the month.

that was based on your input and expected output.

if your example doesn't fully represent what you're trying to to get, please post an expanded example.
NiceMan331

ASKER
ok , what if we replace
 
CEIL(EXTRACT(DAY FROM trans_date) / 7) wk

Open in new window

by this one
 
 to_number(to_char(trans_date,'IW'))

Open in new window

to get week number of the year
Sean Stuber

you can try, the weeks won't match up with what is in your example though
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
NiceMan331

ASKER
it is ok , i try it , it is ok
now let me descripe the last issue

let say week no : 42
store no : 4
sum(trans_amount) of day 1 of w_42 =  10,300
sum(trans_amount) of day 2 of w_42 =  9,300
sum(trans_amount) of day 3 of w_42 =  30
sum(trans_amount) of day 4 of w_42 =  15,300
sum(trans_amount) of day 5 of w_42 =  19,300

here as per the code , it will give sum_amt for w_42 = 54,230
and count = 5
then average = 54,230/5 = 10,846

here i expect the data of day 3 is wrong , i want to execlude any data less than 100 per day / per store
then sum should be : 54,200
day count = 4
and average = 54200/4 = 13550
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NiceMan331

ASKER
yes , it is ok
i'm sorry i had another issue , may be i have to open new stopic for it
our weekend here is : Friday & Saturday
so the first day of the week will be Sunday
while in the system is Monday
how to adjust the code to make it Sunday to Saturday
should i need a function for it ?
Sean Stuber

adjust the day by 1 to shift the week counter to start Sunday

 to_number(to_char(trans_date+1,'IW'))
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NiceMan331

ASKER
yes , it is correct
sorry for delay cause just i attend the office
thanx