Repository navigation
Expand file tree
/
Copy pathSession-08.sql
More file actions
72 lines (50 loc) · 1.82 KB
/
Copy pathSession-08.sql
File metadata and controls
72 lines (50 loc) · 1.82 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- SESSION 8
drop table orders ;
-- Create the Orders table
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
user_id INT,
payment_method VARCHAR(20),
amount DECIMAL(10, 2)
);
-- Insert 8 sample records
INSERT INTO Orders (order_id, user_id, payment_method, amount) VALUES
(1001, 101, 'UPI', 1250.50),
(1002, 102, 'Card', 3499.00),
(1003, 103, 'COD', 899.99),
(1004, 101, 'Wallet', 450.00),
(1005, 104, 'UPI', 2100.75),
(1006, 105, 'Card', 15999.00),
(1007, 102, 'COD', 350.00),
(1008, 106, 'Wallet', 720.25);
select payment_method,
count(order_id) as method from orders
group by payment_method ;
select user_id ,
sum(amount) as total_spend
from orders
group by user_id ;
select payment_method,
avg(amount) as amount from orders
group by payment_method
having avg(amount) > 300 ;
/*
Use WHERE to exclude specific records from the calculation entirely.
Scenario: Calculate the average order amount per payment method,
but ignore any single order that is less than 100
(perhaps filtering out test transactions or cancellations).
Logic: The row with amount < 100 is removed before the average is calculated.
SELECT payment_method, AVG(amount) AS avg_amount
FROM Orders
WHERE amount >= 100 -- Filters individual rows first
GROUP BY payment_method;
Use HAVING to exclude entire categories based on their calculated statistics.
Scenario: Calculate the average order amount per payment method,
but only show methods where the final average is greater than 1000.
Logic: All rows are included in the calculation first;
then, entire payment methods (groups) with a low average are hidden.
SELECT payment_method, AVG(amount) AS avg_amount
FROM Orders
GROUP BY payment_method
HAVING AVG(amount) > 1000; -- Filters the resulting groups
*/