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:
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:
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.