SELECT sums per subcat FROM subquery AS columns

Question

Given 3 tables (Contracts,Services,Contracts_Services)

A Contract as multiple Services organized in 4 types (for now, more will come).

What I need is to get the SUM() of all prices GROUP BY service_type FOR EACH Contract, one row per Contract.

contracts.id,'total_service_type_1','total_service_type_2','total_service_type_3','total_service_type_4'

Contracts

• id

Services

• id
• servicetype_id

Contracts_Services

• contract_id
• service_id
• price

Here is what I got so far

SELECT c.id,s.servicetype_id,sum(cs.price)as total
FROM contracts c
LEFT JOIN addresses a ON a.id = c.address_id
LEFT JOIN contracts_services cs ON cs.contract_id = c.id
LEFT JOIN services s ON s.id = cs.service_id
WHERE s.servicetype_id IS NOT NULL
GROUP BY c.id,s.servicetype_id
ORDER BY c.id

How can I turn these query results into columns for a single Contract row?

Answer 1

You can't have dynamic columns in MySQL (or any SQL-flavor as far as I know). So you can only achieve this by adding every service-type as a new join to your query. I've added two service-types in below example for brevity, for extra service-types just add more LEFT JOIN.

With that last said: there is your pain. This solution is absolutely not flexible. With every new service-type you add, you have to edit your query again. You should really look for a solution within your programming language. Thus when you are calling MySQL from PHP, use your query you already had and was working nearly great, and use PHP to rewrite your array (dynamically) so you get the desired output (replace PHP by the programming language you are using).

SELECT 
  c.id AS contractId, 
  SUM(cs1.price) AS service1Total,
  SUM(cs2.price) AS service2Total
FROM contracts c
JOIN services s
LEFT JOIN contracts_services cs1 ON 
  cs1.contract_id = c.id 
  AND cs1.service_id = s.id
  AND s.servicetype_id = 1
LEFT JOIN contracts_services cs2 ON 
  cs2.contract_id = c.id 
  AND cs2.service_id = s.id
  AND s.servicetype_id = 2
GROUP BY c.id

SQLFiddle: http://sqlfiddle.com/#!9/4f9a9c/2/0

Note: You can replace SUM(cs1.price) with IFNULL(SUM(cs1.price), 0) if you prefer 0 over NULL.

Edit: Alternative query, after reading Paul Spiegel's comment (about pivot tables), with less joins and thus more likely to be friendly to your database-server. However with the same drawback as above query: you have to edit it every time you add a new service-type.

SELECT 
  c.id AS contractId, 
  SUM(IF(s.servicetype_id = 1, cs.price, 0)) AS service1Total,
  SUM(IF(s.servicetype_id = 2, cs.price, 0)) AS service2Total
FROM contracts c
LEFT JOIN contracts_services cs ON cs.contract_id = c.id 
LEFT JOIN services s ON cs.service_id = s.id
GROUP BY c.id