How i can show sums and count of orders for domestic orders and non domastic orders separetly for each customers country?

Question

I have 2 requests and want to combine them into 5 columns like Country_name - count_of_orders_for_domestic - sums_for_domectic - count_of_orders_for_non_domestic - sums_for_non_domestic

domestic count of orders and sums

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

non domestic count of orders and sums

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

I try Union but it makes 3 columns and I cant see the difference between domestic and nondomestic. Then I try to make a new alias but I have a problem with count and sums operations.

Answer 1

You can use filtered aggregation:

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