postgresql, select sum of rows for multiple ids

Question

I have a query with sums I'd like to repeat with variables which are day and consumption_sector.name. I don't know how to simplify my question enough not to lose the point I need. Programatically I'd like to have variables and a loop all into one only select result.

Where I am is with having one result line, I'd like to customize and do multiple for having for example all month of year of give date, or all results for a given date for multiple consumption_sector.name

Result

Query I reached

set search_path to dev;
SELECT
    (SELECT SUM(total)
        FROM daily_unit_big_int 
        INNER JOIN measuring_point on measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector on consumption_sector.id = measuring_point.consumption_sector_id
        WHERE 
        measuring_point.type='devices_logger' AND -- an enumerate
        consumption_sector.name like '%gnacio%' AND
        day = '2020-10-25') AS daily_total, -- a date I'd like to give as parameter/variable
    (SELECT SUM(total)
        FROM daily_unit_big_int 
        INNER JOIN measuring_point ON measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector ON consumption_sector.id = measuring_point.consumption_sector_id
        WHERE 
        measuring_point.type='devices_logger' AND 
        consumption_sector.name like '%gnacio%' AND
        EXTRACT(MONTH FROM day) = '10' AND
        EXTRACT(YEAR FROM day) = '2020'
        ) AS monthly_total,
    (SELECT SUM(total)
        FROM daily_unit_big_int 
        INNER JOIN measuring_point ON measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector ON consumption_sector.id = measuring_point.consumption_sector_id
        WHERE 
        measuring_point.type='devices_logger' AND 
        consumption_sector.name like '%gnacio%' AND
        EXTRACT(YEAR FROM day) = '2020'
        ) AS yearly_total 
    ;

Minimal example with pseudo code to understand the point

$variable_date='2020-10-25'
for $consumption_sector_id in (12,13,46,72) do
    SELECT consumption_sector.id AS consumption_sector_id, SUM(total) AS daily_total
            FROM daily_unit_big_int 
            INNER JOIN measuring_point on measuring_point.id = daily_unit_big_int.measuring_point_id
            INNER JOIN consumption_sector on consumption_sector.id = measuring_point.consumption_sector_id
            WHERE 
            measuring_point.type='devices_logger' AND -- an enumerate
            consumption_sector.id = $consumption_sector_id AND
            day = $variable_date -- a date I'd like to give as parameter/variable
end--for

Result

|consumption_sector_id|daily_total|
|12|100|
|13|1000|
|46|1000|
|72|15468|

Here a fiddle for the example where I retreive all data for a particular day and a particular consumption_sector_id

Answer 1

As you can see, i remodelled your query(only with two to get you an idea how to do it W What you make you make for every SELECT that you have a seperate subquery and join them with cs.id, as you only want certain ids

SELECT * 
FROM
    (SELECT cs.id,SUM(du.total) AS daily_total
            FROM daily_unit_big_int as du
            INNER JOIN measuring_point as mp on mp.id = du.measuring_point_id
            INNER JOIN consumption_sector as cs on cs.id = mp.consumption_sector_id
            WHERE 
            mp.type='devices_logger' AND -- an enumerate
            cs.id in (1,2) AND
            du.day = '2020-01-01' -- a date I'd like to give as parameter/variable
     GROUP BY cs.id) dayly
     INNER JOIN
     (SELECT cs.id,SUM(total) monthly
        FROM daily_unit_big_int du
        INNER JOIN measuring_point mp ON mp.id = du.measuring_point_id
        INNER JOIN consumption_sector cs ON cs.id = mp.consumption_sector_id
        WHERE 
        mp.type='devices_logger' AND 
        cs.id in (1,2) AND
        EXTRACT(MONTH FROM du.day) = '01' AND
        EXTRACT(YEAR FROM du.day) = '2020'
        GROUP BY cs.id
        ) monthly ON dayly.id = monthly.id
       

id | daily_total | id | monthly
-: | ----------: | -: | ------:
 1 |        1555 |  1 |   31665
 2 |        2000 |  2 |    6000

db<>fiddle here