Below is my table structure and record for table_1
| id | user_id | salary | date |
| 1 | 1 | 1100 | 2015-01-16 |
| 2 | 1 | 2100 | 2015-01-17 |
| 3 | 1 | 2200 | 2015-01-18 |
| 4 | 1 | 2100 | 2015-01-19 |
| 5 | 2 | 2000 | 2015-01-20 |
| 6 | 2 | 3000 | 2015-01-17 |
| 7 | 2 | 7000 | 2015-01-18 |
| 8 | 2 | 6000 | 2015-01-19 |
| 9 | 2 | 2000 | 2015-01-21 |
And i am executing following query to get the max, sum and its correspondence date for the records.
select max(salary) , sum(salary),date, user_id from table_1 where year(health.hlt_date) = year(now()) and week(health.hlt_date) = week(now()) group by user_id
Output is following
user_id | max(salary) | sum(salary) | date
1 | 2200 | 7500 | 2015-01-19 |
2 | 7000 | 20000 | 2015-01-17 |
3 | 9000 | 9000 | 2015-01-16 |
max and sum are fetched correctly but dates are not correct , i want the dates for the max salary like for user 1, i want date '2015-01-18'.
Any suggestion or help will be appreciated. Thanks,
I am pretty sure there are more appropriate ways to do this, but this is all I could think of now. Bring the aggregate data from the table_1 and once you get the record having max(salary) and corresponding user_id, join it with itself to find the date of that id and salary. Not very efficient but try something like this:
select A.*, B.date
(select max(salary) salary , sum(salary) totSal, user_id from table_1 where year(health.hlt_date) = year(now()) and week(health.hlt_date) = week(now()) group by user_id) A
inner join
(select date, user_id, salary from table_1) B
on
A.user_id = B.user_id
and
A.salary = B.salary
or may be something like this :
select user_Id, MaxSal, totSal, date
(select max(salary) MaxSal, salary, sum(salary) totSal, user_id, date from table_1 where year(health.hlt_date) = year(now()) and week(health.hlt_date) = week(now()) group by user_id, date) A
where salary = MaxSal
What you need to do is to create a subset of data:
+-----------+-----------+
| user_id | max_sal |
+-----------+-----------+
| 1 | 2200 |
+-----------+-----------+
| 2 | 7000 |
+-----------+-----------+
Then you join the same table again with both columns in the join condition, the query looks like:
SELECT
d.user_id,
m.max_sal,
d.date date_max_sal,
(SELECT SUM(h.salary) FROM table_1 h
WHERE h.user_id = d.user_id /* AND THE OTHER CONDITIONS */) sum_sal
FROM
(
SELECT
user_id,
MAX(salary) max_sal
FROM
table_1
/* WHERE clause */
GROUP BY user_id
) m
JOIN
table_1 d
ON m.user_id = d.user_id AND m.max_sal = d.salary
/* WHERE clause */
I've taken into account that the max value may occur twice, so I used a correlated subquery in the sum. If you want results from a certain period (it looks like that but you're referring to tables which are not in the FROM so I left that out) replace the comments with the WHERE conditions