# how to calculate average from two columns?

Page 1 of 1

## 2 Replies - 1153 Views - Last Post: 21 December 2014 - 03:59 AM

### #1 amture106

Reputation: -1
• Posts: 159
• Joined: 08-September 11

# how to calculate average from two columns?

Posted 02 December 2014 - 09:41 AM

I have two tables:

```-- Insert Company table data
INSERT INTO Company (Comp_Name, YrInc_Rank)
VALUES ('Wal-Mart', 1);
INSERT INTO Company (Comp_Name, YrInc_Rank)
VALUES ('Kohls', 2);
INSERT INTO Company (Comp_Name, YrInc_Rank)
VALUES ('Walgreen', 4);
INSERT INTO Company (Comp_Name, YrInc_Rank)
VALUES ('Bircher Hathaway', 4);
INSERT INTO Company (Comp_Name, YrInc_Rank)
VALUES ('GM', 5);
commit;

-- Insert YearIncome table data
INSERT INTO YearIncome (YrInc_Rank, YrInc_LoInc, YrInc_HiInc)
VALUES (1, 120000, 2000000);
INSERT INTO YearIncome (YrInc_Rank, YrInc_LoInc, YrInc_HiInc)
VALUES (2, 2000100, 9000000);
INSERT INTO YearIncome (YrInc_Rank, YrInc_LoInc, YrInc_HiInc)
VALUES (3, 9000100, 20000000);
INSERT INTO YearIncome (YrInc_Rank,YrInc_LoInc, YrInc_HiInc)
VALUES (4, 20000100, 300000000);
INSERT INTO YearIncome (YrInc_Rank, YrInc_LoInc, YrInc_HiInc)
VALUES (5, 300000100, 9000000000);
commit;

```

and I want to get the company name with the average yearly income.

I did try:

```SELECT Company.Comp_Name "Company Name", avg(YearIncome.YrInc_LoInc) "Average Income" (FROM Company LEFT JOIN YearIncome
ON Company.YrInc_Rank = YearIncome.YrInc_Rank) ORDER BY Company.Comp_Name;

```

but I got several errors. I would appreciate some help.

Thanks!

This post has been edited by amture106: 02 December 2014 - 09:44 AM

Is This A Good Question/Topic? 0

## Replies To: how to calculate average from two columns?

### #2 alexn0

Reputation: 0
• Posts: 1
• Joined: 20-December 14

## Re: how to calculate average from two columns?

Posted 21 December 2014 - 02:12 AM

Hi!
Try it

```SELECT Company.Comp_Name "Company Name", avg(YearIncome.YrInc_LoInc) "Average Income"
(FROM Company LEFT JOIN YearIncome
ON Company.YrInc_Rank = YearIncome.YrInc_Rank)
GROUP BY Company.Comp_Name;
```

### #3 baavgai

• Dreaming Coder

Reputation: 7197
• Posts: 15,004
• Joined: 16-October 07

## Re: how to calculate average from two columns?

Posted 21 December 2014 - 03:59 AM

This actually looks pretty flat. No grouping or anything. You don't have enough data for an average with any meaning, but you could average your high and low...

```select YrInc_Rank, (YrInc_LoInc*YrInc_HiInc)/2 as YrInc_Avg
from YearIncome

```

Given the data, a reasonable answer would be:
```SELECT a.Comp_Name as "Company Name", (b.YrInc_LoInc*b.YrInc_HiInc)/2 as "Average Income"
FROM Company a
LEFT OUTER JOIN YearIncome b
ON a.YrInc_Rank = b.YrInc_Rank
ORDER BY a.Comp_Name

```

Probably the worst key name, ever, btw.

Hope this helps.