Postgres, query the result of a subquery?

Question

I have data which is employee level which I aggregate daily, and then join back to a roster to get the name and manager name, etc. Is there a way to then aggregate this data (with the manager name) and join it back to the original data?

Up until this point works fine. I make one row per system_id per date. I join the system ID with a mapping table to get the Employee ID. I then join that to a roster table to get some Employee Information (including their supervisor name).

SELECT *
FROM 
    (SELECT DISTINCT (system_id)
    FROM schema.fact_table
    WHERE report_date >= '2017-09-01') AS t1
CROSS JOIN 
    (SELECT report_date :: DATE
    FROM generate_series('2017-09-01' :: DATE , '2017-09-10' :: DATE , INTERVAL '1 day') report_date ) AS t2
LEFT JOIN 
    (SELECT *
    FROM schema.dimension_table_1
    WHERE system_id IS NOT NULL
            AND expiration_date > '2017-09-01') AS t3
    ON t1.system_id = t3.system_id
        AND t2.report_date >= t3.effective_date
        AND t2.report_date <= t3.expiration_date
LEFT JOIN 
    (SELECT *
    FROM schema.dimension_table_2
    WHERE expiration_date > '2017-09-01') AS t4
    ON t3.employee_id = t4.employee_id
        AND report_date >= t4.effective_date
        AND report_date <= t4.expiration_date
LEFT JOIN 
    (SELECT report_date AS report_date,
         system_id AS system_id,
         sum(stuff_count) AS stuff_count,

    FROM schema.fact_table_1
    WHERE report_date >= '2017-09-01'
    GROUP BY  report_date, avaya_id, source_database ) AS t5
    ON t2.report_date = t5.report_date
        AND t1.system_id = t5.system_id

I then want to add some columns which have the aggregated daily performance for the employee's team.

LEFT JOIN 
    (SELECT report_date,
         supervisor_employee_id,
         sum(stuff_count) AS supervisor_stuff_count,

    WHERE report_date = '2017-09-01'
    GROUP BY  report_date, supervisor_employee_id) AS t7
    ON supervisor_employee_id = supervisor_employee_id
        AND report_date = report_date

How can I achieve this? I need to somehow give an alias to the entire upper section (daily aggregated employee level data with the supervisor name), and then aggregate that alias on the date + supervisor_id_column instead. I just couldn't get it to work right.

Your last subquery alias is t7 but you use supervisor_employee_id without alias? You need ON T7.supervisor_employee_id = SOMETHING.supervisor_employee_id — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Sep 18, 2017 at 19:27
Sorry, yeah. I had attempted to add aliases but I failed. That is just because I copied and pasted some stuff and forgot to remove it. Ignore my aliases in that final section. — trench
– trench, Commented Sep 18, 2017 at 19:37
mmmm I cant ignore that. If you put correct alias the query should work. But your question is too long I cant do all debug for you. Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Sep 18, 2017 at 20:12
Unrelated, but: distinct is NOT a function. DISTINCT (system_id) is exactly the same thing as DISTINCT system_id — user330315
– user330315, Commented Sep 18, 2017 at 20:33
I originally wrapped my entire first section in (SELECT * FROM ... AS t6). So the top section was t6 and the bottom query was labeled as t7). I kept getting an error that t6 didn't exist though, so I wasn't sure that it was possible. — trench
– trench, Commented Sep 19, 2017 at 12:11

Paul Maxwell · Accepted Answer · 2017-09-19 02:47:31Z

1

I need to somehow give an alias to the entire upper section (daily aggregated employee level data with the supervisor name)

Use WITH (Common Table Expressions) which enable you to "reuse" a query by reference to an alias given to the query e.g.

WITH myCTE as (
....
)
select *
from myCTE

Aggregations can be performed in or after the CTE as needed.

answered Sep 19, 2017 at 2:47

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

1 Comment

trench Over a year ago

I wasn't aware of this but it made it so much easier. I don't know what my issue was before.. perhaps parenthesis or something small. But this cleaned it all up and I am a fan. Thanks

Collectives™ on Stack Overflow

Postgres, query the result of a subquery?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related