mysql script - mathematical solution

Question

I have a table with 16 columns: Id, Product_Id, Sunday, SundayCnt, Monday, MondayCnt,...,SaturdayCnt

As you can see there are weekday columns and weekday counting columns

I would like to average the values of every week day ONLY if the value at weekday counting column is bigger than ZERO

Example

 Sunday=30    SundayCnt=0 
 Monday=27    MondayCnt=2 
 Tuesday=2    TuesdayCnt=0 
 Wednesday=75 WednesdayCnt=0 
 Thursday=2   ThursdayCnt=1 
 Friday=12    FridayCnt=0 
 Saturday=15  SaturdayCnt=22

for this example averge must take only (27+2+15)/3=14.66 because Cnt columns on those days are bigger than 0

Any idea on how to make this on a simple script

Is this an average for row? so for each product?

Juan Carlos Oropeza
– Juan Carlos Oropeza

2015-11-04 16:58:56 +00:00
Commented Nov 4, 2015 at 16:58 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 4, 2015 at 16:58
what type of average are you looking for?

Mm-Art-In
– Mm-Art-In

2015-11-04 17:30:12 +00:00
Commented Nov 4, 2015 at 17:30 — Mm-Art-In
– Mm-Art-In, Commented Nov 4, 2015 at 17:30

Juan Carlos Oropeza · Accepted Answer · 2015-11-04 17:28:34Z

5

This isnt going to be pretty.

  SELECT id, Product_Id, 
         IF( denominator = 0, null, numerator / denominator) as average
  FROM 
     (
      SELECT Id, Product_Id,
         (
             if( SundayCnt=0, 0, Sunday) +
             if( MondayCnt=0, 0, Monday) +
             if( TuesdayCnt=0, 0, Tuesday) +
             if( ThursdayCnt=0, 0, Wednesday) +
             if( FridayCnt=0, 0, Thursday) +
             if( SaturdayCnt=0, 0, Saturday) 
         ) as numerator ,
         (
             if( SundayCnt=0, 0, 1) +
             if( MondayCnt=0, 0, 1) +
             if( TuesdayCnt=0, 0, 1) +
             if( ThursdayCnt=0, 0, 1) +
             if( FridayCnt=0, 0, 1) +
             if( SaturdayCnt=0, 0, 1) 
         ) as denominator
      FROM YourTable
   ) as T

But you should consider change your table to

ID    ProductID   Sales   Counter   Day
1      1          30        0      Sunday
2      1          27        2      Monday
3      1          2         0      Tuesday
4      1          75        0      Wednesday
5      1          2         1      Thursday
6      1          12        0      Friday
7      1          15        22     Saturday

Then your query will be very easy

  SELECT product_id, IF(Count(*) = 0, null, SUM(Sales)/Count(*))
  From YourTable
  WHERE Counter <> 0
  GROUP BY product_id

edited Nov 4, 2015 at 17:28

answered Nov 4, 2015 at 17:04

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MHardwick Over a year ago

Only problem is, that doesn't handle the divide by zero case. Adding an "if" to handle that will be even slightly less pretty, but necessary.

Dan Bracuk Over a year ago

If all the counts are zero, this will throw a divide by zero error.

Gerardo Abdo Over a year ago

I think this is a good option, thank you.I wonder if there is a math function for example, for other purposes I have used pow(columnName,0) this results as 1 always. But not sure if the mathematical formula exists for the weekdays situation I face now.

Juan Carlos Oropeza Over a year ago

@DanBracuk Ok no need to panic, I fix the div by 0.

Juan Carlos Oropeza Over a year ago

@GerardoAbdo there isnt a formula for that because is just special condition. If you put your column as row then you can use aggregated function. Dont know if that is an option for you? But you will get more flexibility with a row design.

|

KevinKirkpatrick · Accepted Answer · 2015-11-04 17:16:12Z

0

EDIT: Sorry, just saw that columns are the actual day-of-week, and you want average per row, not average across rows.

In that case, yeah, you'll need formula that explicitly iterates over each column, adding weekday value to numerator and +1 to denominator when cnt > 0. I think the other anser given here should be sufficient, with one caveat: you should wrap denominator with NULLIF(...,0), to avoid division by 0 errors (in which case, when all cnts are 0, the overall average will evaluate to 0/NULL which will just be NULL)

~~In SQL, aggregate functions (like AVG, SUM, etc) ignore inputs of NULL. So you just need to feed NULL to AVG whenever CNT is 0:~~

~~AVG(CASE WHEN weekday_cnt = 0 THEN NULL ELSE weekday_val END)~~

edited Nov 4, 2015 at 17:16

answered Nov 4, 2015 at 17:06

KevinKirkpatrick

1,4541 gold badge10 silver badges15 bronze badges

3 Comments

Juan Carlos Oropeza Over a year ago

.... As you can see there are weekday columns and weekday counting columns AVG is for rows, no for columns

Juan Carlos Oropeza Over a year ago

I just fix the div by 0

KevinKirkpatrick Over a year ago

Got it - NULLIF() is a handy function, for future ref!

Collectives™ on Stack Overflow

mysql script - mathematical solution

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related