Solved

count transactions within a query

Posted on 2014-11-11
27
245 Views
Last Modified: 2014-11-15
hi
reference to this question
http://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
0
Comment
Question by:NiceMan331
  • 14
  • 11
  • 2
27 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Comment

by:NiceMan331
Comment Utility
Ok , give me 10 minuts plz
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
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

0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> sbsdutbar ?

:)
0
 

Author Comment

by:NiceMan331
Comment Utility
Sorry , by mistake
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 total points
Comment Utility
You only provided data for march:
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.

Open in new window

:p
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:NiceMan331
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> 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?
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you can try, the weeks won't match up with what is in your example though
0
 

Author Comment

by:NiceMan331
Comment Utility
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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
Comment Utility
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,
                           SUM(trans_amount) OVER (PARTITION BY branch_no, TRUNC(trans_date)) daysum,
                           TRUNC(trans_date) dy,
                            to_number(to_char(trans_date,'IW')) wk
                      FROM yourtable t)
             WHERE daysum > 100
          GROUP BY branch_no)
ORDER BY branch_no
0
 

Author Comment

by:NiceMan331
Comment Utility
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 ?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
adjust the day by 1 to shift the week counter to start Sunday

 to_number(to_char(trans_date+1,'IW'))
0
 

Author Comment

by:NiceMan331
Comment Utility
yes , it is correct
sorry for delay cause just i attend the office
thanx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now