Link to home
Create AccountLog in
Avatar of NiceMan331
NiceMan331

asked on

count transactions within a query

hi
reference to this question
https://www.experts-exchange.com/questions/28553461/Pivot-Data-Of-Table-By-Month-Or-Week.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
Avatar of Sean Stuber
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
Avatar of NiceMan331

ASKER

Ok , give me 10 minuts plz
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

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
>> sbsdutbar ?

:)
Sorry , by mistake
SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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
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
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
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
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
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
>> 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?
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
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
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
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
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.
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
you can try, the weeks won't match up with what is in your example though
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
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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 ?
adjust the day by 1 to shift the week counter to start Sunday

 to_number(to_char(trans_date+1,'IW'))
yes , it is correct
sorry for delay cause just i attend the office
thanx