My table looks like this:
Dept Wk1 Wk2
100 25 50
200 25 50
300 25 50
400 25 50
500 25 50
My results need to look like this:
Dept Wk1 Wk2
100 25 50
200 25 50
300 25 50
400 50 100
Things worth noting; the database is Pervasive PSQL10 and I'm using PHP to handle the results. I thought about doing this merge in PHP, but ideally if it could be done in the query that would save me the need for the extra code.
Reason for combining rows is that 2 depts will be treated as one when the data is pulled.
I think I'm overthinking it, but I've explored union, group by, and stuff (which I'm probably using incorrectly) and nothing is giving me what I need.
Any help or direction would be greatly appreciated, because what I've read so far addresses rows that have the same values, but not different.
Here is what my query actually looks like:
SELECT
dept_workcenter,
SUM(case when right(date_sequence,2) between 0 and 7 then hours_worked else 0 end) as wk1,
SUM(case when right(date_sequence,2) between 8 and 14 then hours_worked else 0 end) as wk2,
SUM(case when right(date_sequence,2) between 15 and 21 then hours_worked else 0 end) as wk3,
SUM(case when right(date_sequence,2) between 22 and 28 then hours_worked else 0 end) as wk4,
SUM(case when right(date_sequence,2) between 29 and 31 then hours_worked else 0 end) as wk5
FROM job_detail
WHERE job between '017079' AND '017207'
AND date_sequence BETWEEN '141200' and '141231'
AND dept_workcenter != ''
GROUP BY dept_workcenter
HAVING SUM(hours_worked) <> '0'
AND SUM(hours_worked) > '0.05';
You can use a case statement in the group by:
select (case when dept in (400, 500) then 400 else dept end) as dept,
sum(wk1) as wk1, sum(wk2) as wk2
from table t
group by (case when dept in (400, 500) then 400 else dept end)
order by dept;