Podlicz wartość zamówień każdego klienta w podziale na te złożone bezpośrednio (direct) lub online.
Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_mode, order_total, Wynik: 62 rows
[spoiler title=’Rozwiązanie’ style=’default’ collapse_link=’true’]
- Z podzapytaniami:
WITH subquery AS (
SELECT customer_id,
CASE order_mode
WHEN 'direct’ THEN SUM(order_total)
END AS direct,
CASE order_mode
WHEN 'online’ THEN SUM(order_total)
END AS onlines
FROM orders
GROUP BY customer_id, order_mode
)
SELECT customer_id, NVL(direct, 0) as direct, NVL(onlines, 0) as „ONLINE”
FROM subquery
ORDER BY customer_id;
- Bez podzapytań:
SELECT customer_id,
SUM(
CASE order_mode
WHEN 'direct’
THEN order_total
ELSE 0
END)AS direct,
SUM(
CASE order_mode
WHEN 'online’
THEN order_total
ELSE 0
END)AS „ONLINE”
FROM ORDERS
GROUP BY customer_id, order_mode
ORDER BY customer_id;
[/spoiler]