select substr(A.orderdate, 1, 4) as YY, count(distinct A.customerNumber) as CN, sum(B.priceEach * B.quantityOrdered) as sales,
sum(B.priceEach * B.quantityOrdered) / count(distinct A.customerNumber) as AMV
from classicmodels.orders as A left join classicmodels.orderdetails as B on A.orderNumber = B.orderNumber group by 1;
2003 74 3317348.39 44829.032297
2004 89 4515905.51 50740.511348
2005 44 1770936.71 40248.561591
select substr(A.orderdate, 1, 4) as YY, count(distinct A.customerNumber) as CN, sum(B.priceEach * B.quantityOrdered) as sales,
sum(B.priceEach * B.quantityOrdered) / count(distinct A.customerNumber) as AMV, count(distinct A.orderNumber) as OdN,
sum(B.priceEach * B.quantityOrdered) / count(distinct A.orderNumber) as ATV
from classicmodels.orders as A left join classicmodels.orderdetails as B on A.orderNumber = B.orderNumber group by 1;
2003 74 3317348.39 44829.032297 111 29886.021532
2004 89 4515905.51 50740.511348 151 29906.659007
2005 44 1770936.71 40248.561591 64 27670.886094
select C.country, C.city, sum(B.priceEach * B.quantityOrdered) as sales
from classicmodels.orders as A left join classicmodels.orderdetails as B on A.orderNumber = B.orderNumber
left join classicmodels.customers as C on A.customerNumber = C.customerNumber group by 1, 2;
France Nantes 180887.48
USA Las Vegas 80180.98
Australia Melbourne 180585.07
Norway Stavern 104224.79
USA San Rafael 591827.34
select case when C.country in("USA", "CANADA") then "North America" else "Others" end as GRP, sum(B.priceEach * B.quantityOrdered) as sales
from classicmodels.orders as A left join classicmodels.orderdetails as B on A.orderNumber = B.orderNumber left join
classicmodels.customers as C on A.customerNumber = C.customerNumber group by 1;
Others 6124998.70
North America 3479191.91
