Query:
WITH jobs AS ( SELECT job.id, job.clientid, CONCAT(customer.company, ' ', customer.name_first, ' ', customer.name_last) AS "identity", job.gps_lat, job.gps_long FROM public.ja_jobs AS job JOIN public.ja_customers AS customer ON customer.id = job.customerid WHERE job.clientid = 22 AND job.time_job >= 1422702000 AND job.time_job <= 1456743540
AND NOT job.deleted AND NOT job.templated
), items AS ( SELECT job.identity, COALESCE(bill_item.unit_cost, billable.unit_cost, 0) * bill_item.quantity AS cost, COALESCE(bill_item.unit_price, billable.unit_price, 0) * bill_item.quantity AS revenue, bill_item.quantity AS quantity, note.n_quote_status, bill_item.for_invoicing FROM jobs AS job JOIN public.ja_notes AS note ON note.jobid = job.id AND note.note_type IN ('time', 'part') JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id = note.bill_item_id AND bill_item.for_invoicing LEFT JOIN dm.billables AS billable ON billable.billable_id = note.billable_id JOIN public.ja_mobiusers AS user_creator ON user_creator.id = note.mobiuserid AND ( user_creator.name_first ilike 'Alan' OR user_creator.name_last ilike 'Alan' )
)
SELECT item.identity, SUM(CASE WHEN item.for_invoicing THEN item.revenue ELSE 0 END) AS revenue, SUM(CASE WHEN item.for_invoicing THEN item.quantity ELSE 0 END) AS quantity, SUM(CASE WHEN item.for_invoicing THEN item.cost ELSE 0 END) AS cost
FROM items AS item
GROUP BY item.identity
ORDER BY revenue DESC, item.identity ASC
Explain analyze link: http://explain.depesz.com/s/IIDj