Background:

Table "STATS"
PK "ID" = GUID
PK "WK" = Integer
"A" = Decimal 6,2 - Default = zero
through
"Z" = Decimal 6,2 Default = zero

When pushing data from "file x", it will push the GUID associated with the file, the week # assigned inside the file, then will push a decimal value into the lettered columns. Depending on the file, it may be "A" through "G" or it might be "F" through "L" or it could even be split and be "C" through "G" and "L" through "T".  So not every column will have a value pushed.

Goal is to have a SQL statement that could give me the column averages for all weeks for each "ID" picked from the list of "ID"s

So if "ID" of QQQ for week 1 in A column is 50, week 2 is 100, and week 2 was 100 in A and 200 in B then if that was the only ID, I should see 50 as the ave for A and 150 for ave for B.  Zero's in a row for "A" through "Z" would not be factored in the calculation.

Suggestions?

Lance


Try

select
SUM(A) / SUM(IF( A <> 0,1,0)),
SUM(B) / SUM(IF( B <> 0,1,0))
FROM STATS
GROUP BY ID

you can type the C .. Z lines yourself !!

Roy Lambert

Thanks Roy!

I'll give that a whirl next week.  But disappointed, you didn't give me the examples for C-Z.     

Cheers!

Lance



Roy Lambert wrote:

Lance


Try

select
SUM(A) / SUM(IF( A <> 0,1,0)),
SUM(B) / SUM(IF( B <> 0,1,0))
FROM STATS
GROUP BY ID

you can type the C .. Z lines yourself !!

Roy Lambert

Lance


>I'll give that a whirl next week. But disappointed, you didn't give me the examples for C-Z.

Well I would have done but most of my alphabet is in for repair due to BREXIT

Roy