Select two aggregate functions on the same column using different criteria – performance

aggregateconditionjoin;performanceperformance-tuning

Dear stack exchange users, first question here.

The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many finished, unfinished, total money earned, total money pending from a couple of tables

I'm a bit worried about if this is the optimal way to write this query. It looks like I should be able to shorten this since i'm selecting on the same tables, shouldn't it be possible to reuse that in some way?

            SELECT finished, notfinished, totalamount, areas, pendingamount
            FROM 
            (        
            SELECT COUNT(finished) as finished, COALESCE(SUM(cost),0) as totalamount FROM HiresService as hs
            JOIN ServiceProvider USING(serviceproviderid)
            JOIN BusinessUser USING(businessuserid)
            WHERE BusinessUser.businessuserid = ? AND hs.finished = true
            ) t1
            JOIN 
            (
            SELECT COUNT(finished) as notfinished, COALESCE(SUM(cost),0) as pendingamount  
            FROM HiresService as hs2
            JOIN ServiceProvider USING(serviceproviderid)
            JOIN BusinessUser USING(businessuserid)
            WHERE BusinessUser.businessuserid = ? AND hs2.finished = false
            ) t2
            JOIN
            (
            SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
            FROM ServicesArea 
            JOIN Area USING(areaid) 
            WHERE serviceproviderid IN(
                 SELECT serviceproviderid 
                 FROM ServiceProvider 
                 JOIN BusinessUser USING(businessuserid) 
                 WHERE businessuserid = ?)
            ) t3

Table structure looks like this:

Model

EDIT: (accepted answer)

SELECT finished, notfinished, totalamount, areas, pendingamount  
FROM ( SELECT  
sum(case when finished = 1 THEN 1 ELSE 0 END) as finished, 
sum(case when finished = 1 THEN cost ELSE 0 END) as totalamount, 
sum(case when finished = 0 THEN 1 ELSE 0 END) as notfinished,
sum(case when finished = 0 THEN cost ELSE 0 END) as pendingamount    
FROM HiresService
JOIN ServiceProvider USING(serviceproviderid)
JOIN BusinessUser USING(businessuserid)
WHERE BusinessUser.businessuserid = ?
) t1
JOIN            
(
SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
FROM ServicesArea 
JOIN Area USING(areaid) 
WHERE serviceproviderid IN(
SELECT serviceproviderid 
FROM ServiceProvider 
JOIN BusinessUser USING(businessuserid) 
WHERE businessuserid = ?)
) t2

Best Answer

There is a trick you can use to filter aggregates:

select
 --...
 , sum(case when CONDITION THEN 1 ELSE 0 END) AS FilteredCount
 , sum(case when CONDITION THEN SomeCol ELSE 0 END) AS FilteredSumOfSomeCol

If you can fit your aggregates into this scheme you can avoid the need for repeated query parts like in the current code. It looks like you can use this trick.