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

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

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;
```

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

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 ?

:)

:)

ASKER

Sorry , by mistake

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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.

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

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

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

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

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

ASKER

sdstubar

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

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

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

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

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

which my query almost exactly matches with

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?

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
```

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
```

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?

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 )

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
```

it really result no data , while in fact it has
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

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

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

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.

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

ASKER

ok , what if we replace

`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
you can try, the weeks won't match up with what is in your example though

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

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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 ?

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'))

to_number(to_char(trans_da

ASKER

yes , it is correct

sorry for delay cause just i attend the office

thanx

sorry for delay cause just i attend the office

thanx

even referencing the previous question doesn't help here because there is no sample data in that question either