CODEWITHSUNDEEP
mysqlsql
user62367
user62367

Reputation: 35

Average the value based on count of a column value

I have a table with wrong views, I am expecting my new tables with values below by manipulating the existing table. Basically, My query should be if more than 1 V1 is present then its value should be averaged among A1.

This is what I tried so far,

select VID,AID,Avg(Views)
from T1
having count(V1) > 1

But this is giving me wrong results, I think this should be a subquery, any help on correcting my query.

Available: Table with wrong views

+-----+-----+-------+
| AID | VID | Views |
+-----+-----+-------+
| A1  | V1  |   600 |
| A2  | V1  |   600 |
| A3  | V1  |   600 |
| A4  | V2  |   200 |
| A5  | V2  |   200 |
| A6  | V3  |   50  |
+-----+-----+-------+

Expected output:

+-----+-----+-------+
| AID | VID | Views |
+-----+-----+-------+
| A1  | V1  |   200 |
| A2  | V1  |   200 |
| A3  | V1  |   200 |
| A4  | V2  |   100 |
| A5  | V2  |   100 |
| A6  | V3  |   50  |
+-----+-----+-------+

Upvotes: 1

Views: 41

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You seem to want simple division and a window function:

select aid, VID, Views / count(*) over (partition by vid)
from T1;

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

I think you need a denominator for count that would come from an aggregated subquery :

SELECT T1.AID, T1.VID, T1.Views / cnt AS Views 
  FROM T1
  JOIN ( SELECT VID, count(VID) AS cnt FROM T1 GROUP BY VID ) T2
    ON T2.VID = T1.VID

Demo

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

an aggregation function with columns without aggregation functions need a group by 

select VID,AID,Avg(Views)
from T1
group by VID, AID
having count(V1) > 1

Upvotes: 0

Related Questions