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

#1 amture106  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • 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  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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;

Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1