Multiple aggregates in SQL query

Please help me wrap my head around subqueries (assuming that’s what is needed here). I have a table of orders and customers. I’m looking to write a query that shows me both the number of orders for all customers AND the number of orders for customers with a specific profile.

select count(o.order_no) from orders o
where o.order_dt = '20200320'

and

select count(o.order_no) from orders o
left join customers c on o.cust_no = c.cust_no
where o.order_dt = '20200320' and c.cust_prfl in ('C', 'D')

So I’m looking for two aggregate values at the same time – first the number of orders for set date and then also the same aggregate, but with the additional condition of customers with specific profile. Hence the result should be:

3 2

Additional monkey wrench I have is that I’d even like the count of customer table to be included select count(c.cust_prfl) from customers c where c.cust_prfl in ('C', 'D', 'E') but that’s less of an issue. But in an ideal scenario I’d have 3 2 3

I can’t really utilize the union command to connect separate queries, since I need the result in different columns, not rows.

ORDERS
Order_no   Order_dt   Cust_no
101        20200303     1
102        20200320     3
103        20200320     3
104        20200320     2

CUSTOMERS
Cust_no    Cust_prfl
1          A
2          B
3          C
4          C
5          D

Answer

You can achieve your original requirement with conditional aggregation over your existing query (although just using a JOIN rather than a LEFT JOIN since presumably all orders must have existing customers):

SELECT
  COUNT(*) AS orders,
  COUNT(CASE WHEN Cust_prfl IN ('C', 'D') THEN 1 END) AS orders_cust
FROM (
  SELECT o.order_dt, c.Cust_prfl
  FROM orders o
  JOIN customers c ON c.Cust_no = o.Cust_no
  WHERE o.order_dt = '20200320'  
) o

To get the total number of customers with the different profiles requires a separate subquery, but you can add that to this query too:

SELECT
  COUNT(*) AS orders,
  COUNT(CASE WHEN Cust_prfl IN ('C', 'D') THEN 1 END) AS orders_cust,
  (SELECT COUNT(*) FROM customers WHERE Cust_prfl IN ('C', 'D', 'E')) AS cde_customers
FROM (
  SELECT o.order_dt, c.Cust_prfl
  FROM orders o
  JOIN customers c ON c.Cust_no = o.Cust_no
  WHERE o.order_dt = '20200320'  
) o

Output:

orders  orders_cust     cde_customers
3       2               3

Demo on SQLFiddle