rendered paste bodyQuery: No Group By:
----------------------------------------------------------------
select a.status_id as status_id,
a.type_id as type_id,
a.over_time_policy_id as over_time_policy_id,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date
from user_date_total as a
LEFT JOIN user_date as b ON a.user_date_id = b.id
LEFT JOIN user_wage as z ON z.id = (select z.id
from user_wage as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.deleted = 0
order by z.effective_date desc limit 1)
where
b.user_id = '40'
AND b.pay_period_id = '1103'
AND b.date_stamp <= '2006-01-14'
AND a.status_id in (10,30)
AND ( a.deleted = 0 AND b.deleted = 0)
order by a.status_id desc, a.type_id asc, user_wage_effective_date desc
+-----------+---------+---------------------+--------------+--------------------------+
| status_id | type_id | over_time_policy_id | user_wage_id | user_wage_effective_date |
+-----------+---------+---------------------+--------------+--------------------------+
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 10 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 20 | 0 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 30 | 120 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
| 10 | 40 | 0 | 156 | 2006-01-01 |
+-----------+---------+---------------------+--------------+--------------------------+
54 rows in set (0.00 sec)
Query: With Group By:
----------------------------------------------------------------
select a.status_id as status_id,
a.type_id as type_id,
a.over_time_policy_id as over_time_policy_id,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date,
sum(total_Time) as total_time
from user_date_total as a
LEFT JOIN user_date as b ON a.user_date_id = b.id
LEFT JOIN user_wage as z ON z.id = (select z.id
from user_wage as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.deleted = 0
order by z.effective_date desc limit 1)
where
b.user_id = '40'
AND b.pay_period_id = '1103'
AND b.date_stamp <= '2006-01-14'
AND a.status_id in (10,30)
AND ( a.deleted = 0 AND b.deleted = 0)
group by user_wage_id, user_wage_effective_date, a.status_id, a.type_id, a.over_time_policy_id, a.absence_policy_id, a.premium_policy_id
order by a.status_id desc, a.type_id asc, user_wage_effective_date desc
+-----------+---------+---------------------+--------------+--------------------------+------------+
| status_id | type_id | over_time_policy_id | user_wage_id | user_wage_effective_date | total_time |
+-----------+---------+---------------------+--------------+--------------------------+------------+
| 10 | 10 | 0 | 156 | 2006-01-01 | 421200 |
| 10 | 10 | 0 | NULL | NULL | 32400 |
| 10 | 20 | 0 | 156 | 2006-01-01 | 374400 |
| 10 | 20 | 0 | NULL | NULL | 28800 |
| 10 | 30 | 120 | 156 | 2006-01-01 | 46800 |
| 10 | 30 | 120 | NULL | NULL | 3600 |
| 10 | 40 | 0 | 156 | 2006-01-01 | 97200 |
| 10 | 40 | 0 | NULL | NULL | 32400 |
+-----------+---------+---------------------+--------------+--------------------------+------------+
8 rows in set (0.00 sec)
On PGSQL with Group By:
status_id | type_id | over_time_policy_id | user_wage_id | user_wage_effective_date | total_time
-----------+---------+---------------------+--------------+--------------------------+------------
10 | 10 | 0 | 2159 | 2006-01-01 | 453600
10 | 20 | 0 | 2159 | 2006-01-01 | 403200
10 | 30 | 247 | 2159 | 2006-01-01 | 50400
10 | 40 | 0 | 2159 | 2006-01-01 | 129600
(4 rows)