如何分别显示每个客户国家的国内订单和非国内订单的订单总额和数量

Question

我有两个请求，希望将它们组合成5列，如Country_name-count_of_orders_for_domestic-sums_for_dometic-count_of _of_oorders_for_non_onitial-sums_for _non_onative

国内订单和总额

SELECT co.name country,
count(cu.id) domestic_orders,
sum(o.price) domastic_sum
FROM countries co
JOIN cities c ON co.id=c.country_id
JOIN customers cu ON c.id=cu.city_id
JOIN orders o ON cu.id=o.customer_id
JOIN products AS p ON o.product_id=p.id
WHERE p.country_id=co.id
GROUP BY co.name

非国内订单和总额

SELECT co1.name country,
count(cu1.id) non_domestic_orders,
sum(o1.price) non_domastic_sum
FROM countries co1
JOIN cities c1 ON co1.id=c1.country_id
JOIN customers cu1 ON c1.id=cu1.city_id
JOIN orders o1 ON cu1.id=o1.customer_id
JOIN products AS p1 ON o1.product_id=p1.id
WHERE p1.country_id<>co1.id
GROUP BY co1.name

我尝试了Union，但它有3个专栏，我看不出国内和非国内的区别。然后我尝试创建一个新的别名，但我在计数和求和操作方面遇到了问题。

Answer 1

您可以使用过滤聚合：

SELECT co.name as country,
count(cu.id) filter (where p.country_id = co.id) as domestic_orders,
sum(o.price) filter (where p.country_id = co.id) as domastic_sum,
count(cu.id) filter (where p.country_id <> co.id) as non_domestic_orders,
sum(o.price) filter (where p.country_id <> co.id) as non_domastic_sum
FROM countries co
JOIN cities c ON co.id=c.country_id
JOIN customers cu ON c.id=cu.city_id
JOIN orders o ON cu.id=o.customer_id
JOIN products AS p ON o.product_id=p.id
GROUP BY co.name