CODEWITHSUNDEEP
sqldatabaseoracle-database
viartemev
viartemev

Reputation: 301

Analytic function SQL

For each matter the order of the table CUSTOMERS display the name of the customer (companyname), and the following three columns - the minimum, maximum and average value of orders this customer with the discount (data from tables ORDERS and ORDERDETAILS).
Note: It is assumed that the request will contain only one word SELECT.
The scheme of databases:
enter image description here

select distinct c.companyname, 
sum(od.unitprice*od.quantity*(1-od.discount)) over (partition by o.orderid) as "SUM"
from customers c, orders o, orderdetails od where c.customerid=o.customerid 
and o.orderid=od.orderid

This query displays the total value of orders. As additionally find the minimum, maximum and average value of orders?

select c.companyname, 
min(od.unitprice*od.quantity*(1-od.discount)) over (partition by o.customerid) as "MIN",
max(od.unitprice*od.quantity*(1-od.discount)) over (partition by o.customerid) as "MAX",
avg(od.unitprice*od.quantity*(1-od.discount)) over (partition by o.customerid) as "AVG"
from customers c, orders o, orderdetails od 
where c.customerid=o.customerid and o.orderid=od.orderid

This query finds the minimum, maximum and average cost is not the whole order, but only sub-orders (as in one order may be several lots).

Upvotes: 0

Views: 131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think you just want group by, but twice. Once at the orders level and once at the customer level:

select companyname, min(mino), max(maxo), sum(sumo)
from (select c.companyname, o.orderid,
             min(od.unitprice*od.quantity*(1-od.discount)) as mino
             max(od.unitprice*od.quantity*(1-od.discount)) as maxo
             sum(od.unitprice*od.quantity*(1-od.discount)) as sumo
      from customers c join
           orders o
           on c.customerid = o.customerid join
           orderdetails od 
           on o.orderid = od.orderid
      group by c.companyname, o.orderid
     ) co
group by companyname;

Note that I also introduced explicit join syntax. If you are learning SQL, you should learn the right way to express joins, using the join keyword and the on clause.

EDIT:

One select is a ridiculous limitation. Presuming this is for a course, it is even worse to impose such a restriction where modern join syntax is not a requirement.

But, I think it is possible. Stupid query, but still possible.

      select distinct c.companyname,
             min(min(od.unitprice*od.quantity*(1-od.discount))) over (partition by companyname) as mino,
             max(max(od.unitprice*od.quantity*(1-od.discount))) over (partition by companyname) as maxo
             sum(sum(od.unitprice*od.quantity*(1-od.discount))) over (partition by companyname) as sumo
      from customers c join
           orders o
           on c.customerid = o.customerid join
           orderdetails od 
           on o.orderid = od.orderid
      group by c.companyname, o.orderid

I would never write a query like this. The double nested aggregation is both clearer and probably has better performance. But it is interesting (in a perverse way) that this could be done.

Upvotes: 1

Related Questions