How to merge this to AVG IN one

Two columns or two rows?

select AVG(cast(mv AS float)) as mv_avg,
	AVG(cast(ml AS float)) as ml_avg
 from mlbscores where ev='cal'  AND LEAGE='nhl'

Select allOpen in new window

or

select AVG(cast(mv AS float))  from mlbscores where el='cal'  AND LEAGE='nhl'
union all
select AVG(cast(ml AS float))  from mlbscores where ev='cal'  AND LEAGE='nhl'

Select allOpen in new window

You could UNION them:

select AVG(cast(mv AS float)) AS AVGONE  from mlbscores where el='cal'  AND LEAGE='nhl'
UNION
select AVG(cast(ml AS float))  AS AVGTWO from mlbscores where ev='cal'  AND LEAGE='nhl'

>>You could UNION them:

I would suggest union all to
1:  remove the sort
2:  if the average is the same, union will only return one row

I mean the 2  the average of the 2 results
regards

1 result is 1.6 the other is 4

i want to achive 1.6+4 /2
regards

OK, then just do that?

select (AVG(cast(mv AS float))+AVG(cast(ml AS float)))/2
 from mlbscores where ev='cal'  AND LEAGE='nhl'

Select allOpen in new window

el      ml      ev      mv      fecha      tempo      ident      pl      pv      ipl      ipv      leage
COL 5      CAL       3      03/10/2019      2019            25457                              NHL
CAL      3      LA       4      08/10/2019      2019            25867                              NHL
DAL      2      CAL       3      10/10/2019      2019            25975                              NHL
VEG      6      CAL       2      12/10/2019      2019            26125                              NHL
CAL      3      PHI       1      15/10/2019      2019            26349                              NHL
SJ      3      CAL       1      13/10/2019      2019            26259                              NHL
CAL      3      VAN 0      05/10/2019      2019            25624                               NHL


want the goal average of CAL either is local or visitor (the goals they made)

Yep, I missed the el vs ev = 'cal'.  Ark's post looks like it might work but I didn't set up a test case.

el      ml      ev      mv      fecha      tempo      ident      pl      pv      ipl      ipv      leage
COL      5      CAL      3      03/10/2019      2019            25457                              NHL
CAL      3      LA      4      08/10/2019      2019            25867                              NHL
DAL      2      CAL      3      10/10/2019      2019            25975                              NHL
VEG      6      CAL      2      12/10/2019      2019            26125                              NHL
CAL      3      PHI      1      15/10/2019      2019            26349                              NHL
SJ      3      CAL      1      13/10/2019      2019            26259                              NHL
CAL      5      DET      1      17/10/2019      2019            26434                              NHL
CAL      3      VAN 0      05/10/2019      2019            25624                              NHL



Oh men almost give me the number, suposed to be 2.85 and the query give me 2.75
any idea?

got it
just a little mistake
will be

 SELECT
  AVG(cast(CASE
    WHEN el = 'Clb' THEN ml
    ELSE mv
  END AS float))
FROM mlbscores
WHERE (el = 'Clb' OR ev = 'Clb') AND LEAGE = 'NHL'

Select allOpen in new window

Thank you all!!