postgresql select count but not limited to the where clause-CodePudding

I cannot seem to wrap my head around this 1 and maybe it's not possible in 1 query. This is what I have, but what I want is to see is all purchase_orders with shipment_status between 5 and 25. And the count(email) of ALL purchase_orders. Right now I think this will only work with 2 queries, but I really hope it's possible in 1 query.

select name, email, count(email) as cnt_email
from purchase_orders
where shipment_status>5 AND shipment_status<25
group by email, name
having count(email) > 1
order by name

Edit 1: Some background information. We don't require people to register with us to sell to us. All data is in the purchase_orders table. We identify different users by their emailaddress, although it would be perfectly fine if someone is a returning seller and uses a different emailaddress.

Edit 2: if I would write to queries i would write:

select name, email from purchase_orders where shipment_status> 5 and shipment_status<25

and


select count(email) as cnt_email from purchase_orders having count(email) > 1

I hope that makes it clear.

Thanks for any help!

CodePudding user response：

If I understand your problem you can use count(*) filter (where ...) format to resolve your problem.

select 
  name, 
  email, 
  count(email) as cnt_email,
  count(email) filter (where shipment_status>5 AND shipment_status<25) as cnt_email_condition
from purchase_orders
group by email, name
having count(email) > 1
order by name

CodePudding user response：

I made the query i wanted. I'm not going to rewrite to make it fit with the example i used in the question. Sorry for laziness.

select
    po.id, po.name, po.email,
    case
        when po.last_total_price/po.last_total_quantity > 400 then 'Gemiddeld hoge prijs'
        when po.last_total_quantity - count(distinct pol.product_id) > 0 then 'Meerdere exemplaren'
        when (select count(email) from purchase_orders where email=po.email group by email) > 10 then 'Terugkerende verkoper'
    end as afwijking
from
    purchase_orders po
left join
    purchase_order_lines pol on pol.purchase_order_id=po.id
where
    po.shipment_status between 5 AND 25
group by
    po.id
order by
    po.name