CODEWITHSUNDEEP
sqlpostgresqlcountsumaggregate-functions
Preston G
Preston G

Reputation: 69

Left Join To Separate Queries From Same View

I am still new to learning PSQL and was wondering how to LEFT JOIN two separate queries that I am retrieving from the same materialized view.

Here is my first query:

select rep_name, Count(enroll_date) new_orders, Sum(invoice_total) contract_total, Avg(invoice_total) avg_contract_total
from recent_billing_mat_view
where
    enroll_date <= current_date
    AND enroll_date > (current_date - '30 days'::interval)
    AND product_type = 'CTF'
    AND pay_type <> 'Credit'
group by rep_name

And it returns the following:

rep_name   new_orders  contract_total avg_contract_total
"Alyssa"    9               2515       279.444444444444
"Carlos"    24              6585       274.375
"Cheryle"   14              4871       347.928571428571
"Nicholas"  19              4775       251.315789473684
"Piero"     13              4405.5     338.884615384615
"Susan"     15              4450.5     296.7
"Valerie"   16              4640       290
"Yelitza"   12              3607       300.583333333333

Here is my second query:

select rep_name,  Count(enroll_date) orders_paid
from recent_billing_mat_view
where
    enroll_date <= current_date
    AND enroll_date > (current_date - '30 days'::interval)
    AND product_type = 'CTF'
    AND pay_type <> 'Credit'
    AND amount_paid > 0
group by rep_name

And it returns the following:

rep_name    orders_paid
"Alyssa"        6
"Carlos"        16
"Cheryle"       8
"Nicholas"      14
"Piero"         9
"Susan"         8
"Valerie"       10
"Yelitza"       9

I am using the 2nd query to get a count of all orders that have paid for each rep and I want to join that on the first query. I am currently getting the error: ERROR: syntax error at or near "LEFT".

Here is what I am working with:

select r.rep_name, Count(enroll_date) new_orders, Sum(invoice_total) contract_total, Avg(invoice_total) avg_contract_total
from recent_billing_mat_view r
where
    r.enroll_date <= current_date
    AND r.enroll_date > (current_date - '30 days'::interval)
    AND r.product_type = 'CTF'
    AND r.pay_type <> 'Credit'
group by r.rep_name
LEFT JOIN (
select Count(enroll_date) orders_paid
from recent_billing_mat_view p
where
    p.enroll_date <= current_date
    AND p.enroll_date > (current_date - '30 days'::interval)
    AND p.product_type = 'CTF'
    AND p.pay_type <> 'Credit'
    AND p.amount_paid > 0
group by rep_name
) as p On r.rep_name = p.rep_name

What I would like the results to look like:

rep_name   new_orders  contract_total avg_contract_total  orders_paid
"Alyssa"    9               2515       279.444444444444        6
"Carlos"    24              6585       274.375                 16
"Cheryle"   14              4871       347.928571428571        8
"Nicholas"  19              4775       251.315789473684        14
"Piero"     13              4405.5     338.884615384615        9
"Susan"     15              4450.5     296.7                   8
"Valerie"   16              4640       290                     10
"Yelitza"   12              3607       300.583333333333        9

What am I missing here? Is there a better way to do this without the LEFT JOIN?

Thanks!

Upvotes: 1

Views: 21

Answers (1)

GMB
GMB

Reputation: 222722

The second query is quite identical to the first one, excepted for one condition in the where clause. You can just use conditional aggregation:

select rep_name, 
    count(*) new_orders, 
    sum(invoice_total) contract_total, 
    avg(invoice_total) avg_contract_total,
    count(*) filter(where amount_paid > 0) orders_paid
from recent_billing_mat_view
where
    enroll_date <= current_date
    and enroll_date > current_date - '30 days'::interval
    and product_type = 'CTF'
    and pay_type <> 'Credit'
group by rep_name

Note: the way your query is built, enroll_date can never be null (because it is filterd in the where clause): so count(enroll_date) is equivalent to count(*), and more efficient as well.

Upvotes: 2

Related Questions