Postgres return multiple identical results based on the number of years

Question

I have a Postgres query containing subqueries and outputs only 12 rows of data, exactly how I'd like it to look. Each of these rows represents month averaging of data over all the years in the database, one row for each month. The query:

SELECT
  to_char(to_timestamp(to_char(subquery2.month, '999'), 'MM'), 'Mon') AS Month,
  subquery2.month AS Month_num,
  round(AVG(subquery2.all_use_tot), 2) AS Average_Withdrawals,
  round(AVG(subquery2.all_cu_tot), 2) AS Average_Consumptive_Use
FROM
(SELECT
  subquery1.month,
  subquery1.year,
  sum(subquery1.liv_tot) AS all_use_tot,
  sum(subquery1.CU_total) AS all_cu_tot
FROM
    (SELECT 
        EXTRACT('Month' FROM withdrawals.begintime) AS month,
        EXTRACT(YEAR FROM withdrawals.begintime)::int AS year,
        tdx_ic_use_type.use_type_code AS Use_type,
        sum(withdrawals.withdrawalvalue_mgd) AS liv_tot,
        (tdx_ic_use_type.cufactor)*(sum(withdrawals.withdrawalvalue_mgd)) AS CU_total
    FROM 
        medford.tdx_ic_use_type,
        medford.withdrawalsites
        JOIN medford.subshed2 ON ST_Contains(medford.subshed2.the_geom, medford.withdrawalsites.the_geom)
        JOIN medford.withdrawals ON medford.withdrawals.ic_site_id = medford.withdrawalsites.ic_site_id
    WHERE 
        subshed2.id = '${param.shed_id}' AND
        withdrawalsites.ic_pr_use_type_id = tdx_ic_use_type.use_type_code AND
        withdrawals.intervalcodes_id = 2

    GROUP BY
        year, 
        extract('Month' from withdrawals.begintime),
        tdx_ic_use_type.cufactor,
        Use_type
    ORDER BY
        year, extract('Month' from withdrawals.begintime) ASC
        ) AS subquery1
GROUP BY
  subquery1.year,
  subquery1.month
  ) AS subquery2
GROUP BY
  subquery2.month
ORDER BY
  subquery2.month

What the output looks like:

"Jan"  1  1426.50  472.65
"Feb"  2  1449.00  482.10
"Mar"  3  1459.50  485.55
"Apr"  4  1470.00  489.00
"May"  5  1480.50  492.45
"Jun"  6  1491.00  495.90
"Jul"  7  1489.50  493.35
"Aug"  8  1512.00  502.80
"Sep"  9  1510.50  500.25
"Oct" 10  1533.00  509.70
"Nov" 11  1543.50  513.15
"Dec" 12  1542.00  510.60

The month column I've extracted from a datetime column in the database. I'm using the results of this query and storing it in an array, but I'd like to repeat these exact results once for each year that exists in the table. So, if there are 2 years of data in the database, there should be 24 output rows, or, 12 of the same rows repeated twice. If there are 3 years of data in the database, there should be 36 output rows, or 12 of the same rows repeated 3 times. How do I accomplish this in my query? Is there a way to loop a query based on values in a column (i.e. the number of years present in a datetime field? )

Community · Accepted Answer · 2017-05-23 12:21:08Z

2

You should change group by month to group by year, month in your query. (But this will give you only results where there are data for the given month.)

For iterating through months:

select a::date
from generate_series(
    '2011-06-01'::date,
    '2012-06-01',
    '1 month'
) s(a)

(based on this: Writing a function in SQL to loop through a date range in a UDF)

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Oct 13, 2013 at 20:11

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jan Over a year ago

Thanks for your thoughts. I've updated my question with the actual query. I tried your suggestion in several different ways, but the values change in different years, so the same 12 month set of data is not repeating, it's changing. Is there some way to loop a query based on values in a column?

Lajos Veres Over a year ago

I added an example to generate months. You should join with this result set.

Collectives™ on Stack Overflow

Postgres return multiple identical results based on the number of years

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related