-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathquestion3_A_1.sql
95 lines (83 loc) · 4.03 KB
/
question3_A_1.sql
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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
SELECT d.title as district,
br.id as branchID,
c.title as province,
br.title as branchName,
sum(sl.amount * b.price) as totalProfit ,
finalMax.salesmanName ,
finalMax.salesmanSurname ,
finalMax.totalSale ,
finalMin.salesmanName as worstSalesmanName ,
finalMin.salesmanSurname as worstSalesmanSurname,
finalMin.totalSale as worstTotalSale
from bedirhan_bardakci.sale sl
join bedirhan_bardakci.stock st on st.id = sl.stock_id
join bedirhan_bardakci.book b on b.id = st.book_id
join bedirhan_bardakci.salesman sman on sman.id = sl.salesman_id
join bedirhan_bardakci.branch br on br.id = st.branch_id
join bedirhan_bardakci.city c on c.id = br.city_id
join bedirhan_bardakci.district d on d.id = c.district_id and d.id = :param_id
LEFT JOIN
(
Select salesAmaountSub.branchID , salesAmaountSub.totalSale , r.salesmanName, r.salesmanSurname
from
(
Select salesAmount.branchID , MAX(salesAmount.totalSale) as totalSale
FROM (
SELECT sman.forename as salesmanName, sman.surname as salesmanSurname , br.id as branchID, sum(sl.amount) as totalSale
from bedirhan_bardakci.sale sl
join bedirhan_bardakci.stock st on st.id = sl.stock_id
join bedirhan_bardakci.book b on b.id = st.book_id
join bedirhan_bardakci.salesman sman on sman.id = sl.salesman_id
join bedirhan_bardakci.branch br on br.id = st.branch_id
join bedirhan_bardakci.city c on c.id = br.city_id
join bedirhan_bardakci.district d on d.id = c.district_id and d.id = :param_id
group by sman.forename, sman.surname, br.id )
as salesAmount
group by salesAmount.branchID
) salesAmaountSub
, (
SELECT sman.id as salesmanID, sman.forename as salesmanName, sman.surname as salesmanSurname , br.id as branchID , Count(sman.id) as totalSale
from bedirhan_bardakci.sale sl
join bedirhan_bardakci.stock st on st.id = sl.stock_id
join bedirhan_bardakci.book b on b.id = st.book_id
join bedirhan_bardakci.salesman sman on sman.id = sl.salesman_id
join bedirhan_bardakci.branch br on br.id = st.branch_id
join bedirhan_bardakci.city c on c.id = br.city_id
join bedirhan_bardakci.district d on d.id = c.district_id and d.id = :param_id
group by sman.id,sman.forename,sman.surname ,br.id
) r where r.totalSale = salesAmaountSub.totalSale
and r.branchID = salesAmaountSub.branchID
) finalMax on finalMax.branchID = br.id
LEFT JOIN
(
Select salesAmaountSub.branchID , salesAmaountSub.totalSale , r.salesmanName, r.salesmanSurname
from
(
Select salesAmount.branchID , MIN(salesAmount.totalSale) as totalSale
FROM (
SELECT sman.forename as salesmanName, sman.surname as salesmanSurname , br.id as branchID, sum(sl.amount) as totalSale
from bedirhan_bardakci.sale sl
join bedirhan_bardakci.stock st on st.id = sl.stock_id
join bedirhan_bardakci.book b on b.id = st.book_id
join bedirhan_bardakci.salesman sman on sman.id = sl.salesman_id
join bedirhan_bardakci.branch br on br.id = st.branch_id
join bedirhan_bardakci.city c on c.id = br.city_id
join bedirhan_bardakci.district d on d.id = c.district_id and d.id = :param_id
group by sman.forename, sman.surname, br.id )
as salesAmount
group by salesAmount.branchID
) salesAmaountSub
, (
SELECT sman.id as salesmanID, sman.forename as salesmanName, sman.surname as salesmanSurname , br.id as branchID , Count(sman.id) as totalSale
from bedirhan_bardakci.sale sl
join bedirhan_bardakci.stock st on st.id = sl.stock_id
join bedirhan_bardakci.book b on b.id = st.book_id
join bedirhan_bardakci.salesman sman on sman.id = sl.salesman_id
join bedirhan_bardakci.branch br on br.id = st.branch_id
join bedirhan_bardakci.city c on c.id = br.city_id
join bedirhan_bardakci.district d on d.id = c.district_id and d.id = :param_id
group by sman.id,sman.forename,sman.surname ,br.id
) r where r.totalSale = salesAmaountSub.totalSale
and r.branchID = salesAmaountSub.branchID
) finalMin on finalMin.branchID = br.id
group by d.title, br.id, branchID, c.title, br.title,finalMax.salesmanName , finalMax.salesmanSurname , finalMax.totalSale , finalMin.salesmanName , finalMin.salesmanSurname , finalMin.totalSale