History for Question14

??changed:
-
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

<hr solid id=comments_below>


mj (Sep 17, 2001 7:45 pm; Comment #1)  --
 This one is next on my agenda.