CODEWITHSUNDEEP
mysqlselectsummax
maphaneuf
maphaneuf

Reputation: 77

MYSQL SELECT MAXIMUM RECORD BY USERS ONLY IF USER AS THE HIGEST VALUE FOR THIS ID

I have 1 table that I need to have a query made on it.
I am looking to retreive the sum of all the higest value I have place on each veh_id only if I have the highest value for that veh_id. Here is my table structure and data: 

tablename
id               vehid             userid            amount
1                  1                  3                4
2                  1                  4                5
3                  1                  5                6
4                  2                  3                5
5                  2                  4                4
6                  3                  5                12

If I am looking data for userid:3 expecting result 5
If I am looking for userid:4 expecting result 0
If I am looking for userid:5 expection result 18
I have no clue where to start.

Here is what I have tried. 

SELECT SUM(amount) FROM tablename WHERE user_id = 3 AND amount = (SELECT max(amount)     FROM tablename GROUP BY veh_id)

Upvotes: 1

Views: 165

Answers (1)

Strawberry
Strawberry

Reputation: 33935

CREATE TABLE tablename
(id               INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,vehid             INT NOT NULL
,userid            INT NOT NULL
,amount INT NOT NULL
);

INSERT INTO tablename VALUES 
(1                  ,1                  ,3                ,4),
(2                  ,1                  ,4                ,5),
(3                  ,1                  ,5                ,6),
(4                  ,2                  ,3                ,5),
(5                  ,2                  ,4                ,4),
(6                  ,3                  ,5                ,12);

SELECT x.userid
     , COALESCE(SUM(y.max_amount),0) amount 
  FROM tablename x 
  LEFT 
  JOIN (SELECT vehid,MAX(amount) max_amount FROM tablename GROUP BY vehid) y 
    ON y.vehid = x.vehid 
   AND y.max_amount = x.amount 
 GROUP 
    BY userid;

+--------+--------+
| userid | amount |
+--------+--------+
|      3 |      5 |
|      4 |      0 |
|      5 |     18 |
+--------+--------+

http://www.sqlfiddle.com/#!2/6f2cd/1

Upvotes: 1

Related Questions