Calculate Average of the largest values in multiple columns

Question

I have this table

[1|Sub1|Mark1|Sub2|Mark2|Subj3|Mark3|...]
[2|JSP |85   |ASP |70   |PHP  |95   |...]

I need the average Marks of the two highest subjects?

For example

avg(Mark1,Mark3);

[Check how to ask](https://stackoverflow.com/questions/53372997/pivot-query-on-distinct-records) — Prashant Pimpale, Nov 07 '19 at 07:19
First, fix your broken schema; a database table is not a spreadsheet. — Strawberry, Nov 07 '19 at 09:42

Vignesh Kumar A · Answer 1 · 2019-11-07T08:41:15.927

1

May be are you asking this?

SELECT ID,
  (SELECT AVG(v) 
   FROM (VALUES (Mark1), (Mark2), (Mark3), (Mark4), (Mark5)) AS value(v)) as [AverageMarks]
FROM Table1

If you are looking for only Average of 2 highest subjects among 3 subjects then try this.

SELECT ID,
  (SELECT (SUM(v)-MIN(V))/2
   FROM (VALUES (Mark1), (Mark2), (Mark3)) AS value(v)) as [AverageMarks]
FROM Table1

FIDDLE DEMO

edited Nov 07 '19 at 08:41

answered Nov 07 '19 at 07:22

Vignesh Kumar A

27,863
13
63
115

3

[How to Answer](https://stackoverflow.com/help/how-to-answer) strongly recommends only answering well-asked questions. – Andreas Nov 07 '19 at 07:24
Thank you @VigneshKumarA So what if I only need the Avg of largest three only? – Abdifatah Abdilahi Nov 07 '19 at 08:05
1

Amount 5 subject, do you want to find the average of highest 3 subjects? – Vignesh Kumar A Nov 07 '19 at 08:13
Please edit your question and update all these information. Also post the sample data. – Vignesh Kumar A Nov 07 '19 at 08:22
Be clear on your ask. You want to find average of highest 2 subject or 3 subjects.. @AbdifatahAbdilahi – Vignesh Kumar A Nov 07 '19 at 08:38
@VigneshKumarA highest 2 subjects. – Abdifatah Abdilahi Nov 07 '19 at 08:50
1

Updated my answer. check it out – Vignesh Kumar A Nov 07 '19 at 08:51
Thank you @VigneshKumarA you helped me a lot. Is it Same scenario if there is 7 subjects and I want the avg of highest 5. – Abdifatah Abdilahi Nov 07 '19 at 09:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/201996/discussion-between-abdifatah-abdilahi-and-vignesh-kumar-a). – Abdifatah Abdilahi Nov 07 '19 at 09:35
@VigneshKumarA are you there please what if my subjects are 7 and i want highest 5. – Abdifatah Abdilahi Nov 09 '19 at 10:39

score 1 · Answer 2 · answered Nov 07 '19 at 09:49

1

Consider the following:

DROP TABLE IF EXIST my_table;

CREATE TABLE my_table
(subject CHAR(3) PRIMARY KEY
,mark INT NOT NULL
);

INSERT INTO my_table VALUES
('JSP',85),
('ASP',70),
('PHP',95);   

SELECT AVG(a.mark)
  FROM 
     ( SELECT mark FROM my_table ORDER BY mark DESC LIMIT 2 ) a;

+-------------+
| AVG(a.mark) |
+-------------+
|     90.0000 |
+-------------+

answered Nov 07 '19 at 09:49

Strawberry

33,750
13
40
57

I think, you've missed the table structure and original ask of the user !! – Vignesh Kumar A Nov 07 '19 at 10:01
@VigneshKumarA No. The OP has missed the point of what a relational database is, and how it works. Our role is not to provide sticking plasters for these kinds of errors, but to correct them. – Strawberry Nov 07 '19 at 10:03
First of all he want to find Average among columns not rows – Vignesh Kumar A Nov 07 '19 at 10:07
...which is not something you would ever need or want to do with well-formatted data. – Strawberry Nov 07 '19 at 10:10

Calculate Average of the largest values in multiple columns

avg(Mark1,Mark3);

2 Answers2