How to merge this to AVG IN one

Ernesto
Ernesto used Ask the Experts™
on
select AVG(cast(mv AS float))  from mlbscores where el='cal'  AND LEAGE='nhl'

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

Hi you experts how to merge this 2 results in just one
regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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'

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

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
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'
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
I mean the 2  the average of the 2 results
regards

Author

Commented:
1 result is 1.6 the other is 4

i want to achive 1.6+4 /2
regards
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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'

Open in new window

Author

Commented:
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)
SELECT
  AVG(CAST(CASE
    WHEN el = 'cal' THEN mv
    ELSE ml
  END AS float))
FROM mlbscores
WHERE (el = 'cal' OR ev = 'cal') AND LEAGE = 'nhl'
Most Valuable Expert 2012
Distinguished Expert 2018

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

Author

Commented:
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?

Author

Commented:
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'

Open in new window

Author

Commented:
Thank you all!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial