CODEWITHSUNDEEP
mysql
user1357015
user1357015

Reputation: 11686

Average a column based upon the value in another column SQL

Suppose I have the following data

SqlUnixTime BID    ASK   VALID ASSET_ID
1504900871  101.50 101.6  Y     XY1
1504900870  0      101.6  Y     XY1
1504900871  101.50 20     N     XY1
...

In the BID & ASK columns I can have a valid price, a 0 (meaning no data) or an invalid price (see the final row).

I'd like to compute a 30 day average. I have managed to handle the 0 case using the following query:

Select ASSET_ID, AVG(NULLIF(BID,0)) as AVG_BID_30D, AVG(NULLIF(ASK,0)) as AVG_ASK_30D FROM myDB.myTable where SqlUnixTime > 1504900870 GROUP BY ASSET_ID;

However, how would I only average those values where VALID = "Y". I thought about putting a where clause in the end but then it might not select asset_id that are invalid? I just want it to have a null?

Upvotes: 1

Views: 714

Answers (1)

Agniveer
Agniveer

Reputation: 371

UPDATED

group it by (ASSET_ID, VALID='Y') and then the resultant again group by VALID='Y'

I think it will work.

select A.ASSET_ID, A.AVG_BID_30D, A.AVG_ASK_30D
from (Select ASSET_ID, AVG(NULLIF(BID,0)) as AVG_BID_30D, AVG(NULLIF (ASK,0)) as AVG_ASK_30D, VALID 
FROM myDB.myTable where SqlUnixTime > 1504900870
GROUP BY ASSET_ID, VALID='Y') as A
group by ASSET_ID='Y';

.

Upvotes: 2

Related Questions