-2

I wanted to know how to get the distinct sum of products

The table I have

Name Rating
 A     3
 A     3
 B     4
 C     3
 C     3

The final result I want is SUM(case when distinct table1.name then rating)/Count(case when distinct table1.name then rating) final answer = (3+4+3)/3 = 10/3 = 3.33

Thanks in advance

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Anurag Karanam
  • 1
  • 1

2 Answers2

0

SUM/COUNT <=> AVG so

SqlFiddleDemo

SELECT (SUM(Rating) * 1.0)/COUNT(*)  AS result
FROM (
  SELECT DISTINCT *
  FROM tab
  ) AS sub;

can be:

SqlFiddleDemo2

SELECT AVG(Rating)  AS result
FROM (
  SELECT DISTINCT *
  FROM tab
  ) AS sub;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

Just an other perspective.

Find average rating for each Name and then average of grouped.

Query

select avg(t.`avg`) as `avg` from
(
  select Name,
  avg(rating) as `avg`
  from your_table_name
  group by Name
)t;

SQL Fiddle

Ullas
  • 11,450
  • 4
  • 33
  • 50
  • This another perspective it totally broken. Example **[Demo](https://data.stackexchange.com/stackoverflow/query/374729)**. Average for averages is not the same as Average of set of data. In your specific example where all data inside group are the same it works, but when you have different values it will fail for example (A,3), (A,1). – Lukasz Szozda Oct 12 '15 at 09:40
  • @lad2025: It should be 3.1111 right in the sample created. – Ullas Oct 12 '15 at 09:43
  • Let's calculate (3 + 4 + 3 + 1) / 4 = 2.75 – Lukasz Szozda Oct 12 '15 at 09:43