Question14

Question 14

For each agent taking an order, aid and aname, list the product pid and pname, and the total quantity ordered for orders placed by all customers through that agent. Sort the output by aid (ascending) and then pid (by total quantity ordered descending). The output heading will be:

    AID Agent Name      PID     Product Name    Total Quantity Ordered
    ------------------------------------------------------------------
    Asc                                         Desc. within aid

SQL:

    select oll.aid, aname, oll.pid, pname, sum(dollars) as "Total Quantity Ordered"
        from agent, ord_order_line as oll, product
        where agent.aid = oll.aid
            and product.pid = oll.pid
        group by oll.pid, oll.aid, aname, pname
        order by oll.aid, "Total Quantity Ordered" desc;

Output:

    a01 Smith   p01     comb    1400.50
    a01 Smith   p05     pencil  1100.00
    a01 Smith   p03     razor   700.00
    a01 Smith   p07     case    600.00
    a02 Jones   p06     folder  720.00
    a02 Jones   p02     brush   180.00
    a03 Brown   p05     pencil  2208.00
    a03 Brown   p07     case    1200.00
    a03 Brown   p03     razor   880.00
    a03 Brown   p06     folder  720.00
    a03 Brown   p04     pen     540.00
    a04 Gray    p05     pencil  450.00
    a05 Otasi   p05     pencil  1200.00
    a05 Otasi   p06     folder  720.00
    a05 Otasi   p07     case    720.00
    a05 Otasi   p03     razor   704.00
    a06 Smith   p06     folder  1425.00
    a06 Smith   p01     comb    861.60
    a06 Smith   p03     razor   540.00
    a06 Smith   p05     pencil  400.00

mj (Sep 17, 2001 7:45 pm; Comment #1)

This one is next on my agenda.