DISTINCT and AVG in mysql query [on hold]

61
October 05, 2018, at 4:30 PM

I have this mysql query:

SELECT round(AVG(DISTINCT points),0) as PM
FROM data
WHERE points != 0

In data db I have several users belonging to a different districts. Every user can be in as many districts as they want, but the points for every district is the same, so, we can have:

User , District , Points
   1 ,        1 ,     20 
   1 ,        3 ,     20 
   1 ,       21 ,     20 
   2 ,        3 ,     10
   2 ,        7 ,     10

I would like to extract the Average of points of the table above but only counting every user once regardless of how many districts he is in.

With my query I only get those with different points, but, for example, if I would have a User 3 with 10 points, the query will return: 15 (20+10 = 30/2 = 15), instead of the correct value: 13 (20+10+10 = 40 / 3 = 13,3333).

Any help, please?

Thanks a lot!

Answer 1
  • Firstly, get the points for each user in a Derived table, using group by.
  • Now, using the derived table, compute the Average. You don't need to use Distinct now.

Try (assuming user is denoted by column user_id - change it if different):

SELECT ROUND(AVG(`dt`.`user_points`),0) as PM 
FROM 
(
  SELECT `user_id`, 
         MAX(`points`) AS `user_points`  
  FROM `data`
  WHERE `points` != 0 
  GROUP BY `user_id` 
) AS `dt`
Answer 2

Have you tried GROUP BY on the end of your query?

Working Online Sample Demo at SQLFiddle

Build schema...

CREATE TABLE UserPoints (
  id INT(11) NOT NULL AUTO_INCREMENT,
  Userid INT(11) NOT NULL DEFAULT 0,
  Districtid INT(11) NOT NULL DEFAULT 0,
  PointsCount INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

Insert some sample data...

INSERT INTO UserPoints (Userid, Districtid, PointsCount) VALUES (1, 1, 10);
INSERT INTO UserPoints (Userid, Districtid, PointsCount) VALUES (1, 2, 20);
INSERT INTO UserPoints (Userid, Districtid, PointsCount) VALUES (2, 1, 12);

Then select with GROUP BY...

SELECT Userid, AVG(PointsCount) FROM UserPoints GROUP BY Userid;

Results...

Userid  AVG(PointsCount)
1       15
2       12
READ ALSO
how to grouped column and get max value of these repeated

how to grouped column and get max value of these repeated

I have column has repeated values, I want to group them and get the max value of these grouped because the column has Repeated values, so I want to know how many the value has repeated then get the max value from these grouped values

28
Database model for car-service [on hold]

Database model for car-service [on hold]

I am working on a project for car-service(denting, painting)In this app user will select their location and then select car brand,model and fuel type

56
Opinions please - switching legacy DB from CHAR(14) PKs to INT

Opinions please - switching legacy DB from CHAR(14) PKs to INT

I am administering a MySQL db for a payments processing systemFor various legacy reasons it was originally built using CHAR(14) for many of the primary keys, which store a sequential ID based on a prefix identifying the type of data followed by a base36...

24
Why cant I install mysqlclient==1.3.10

Why cant I install mysqlclient==1.3.10

I'm using a new MacOS High Sierra 1013

26