1

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

pgAdminResult

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

0

1 Answer 1

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

2
  • How would you merge the id columns into the result and renaminng them by consumption_sector.id? Nov 27, 2020 at 15:47
  • 1
    Both are consumption_sector.id make a SELECT dayly.id, dayly.daily_total,monthly.monthly instead of a SELECT *. SELECT * is also not a good style
    – nbk
    Nov 27, 2020 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.