History for Question5

??changed:
-
Question 5

  Repeat query 4 (see [Question4]), but first group the results by agent 
  id, 'aid', and agent name, 'aname', giving the number of orders placed 
  through each agent for each month.  The column heading should be::

    AGENT ID     AGENT NAME      MONTH      NUMBER OF ORDERS     TOTAL DOLLAR SALES

  SQL::

    select ool.aid, aname, month, 
           count(distinct ordno) as "Number Of Orders", 
           sum(dollars) as "Total Dollar Sales"
        from ord_order_line ool, agent, customer
        where ool.aid = agent.aid
            and ool.cid = customer.cid 
            and customer.city = 'Duluth'
        group by ool.aid, aname, month, percent
        order by percent desc;

  Output::

    a04	Gray	mar	1	450.00
    a03	Brown	feb	1	1860.00
    a01	Smith	jan	2	3300.00
    a02	Jones	feb	1	900.00
    a05	Otasi	apr	1	720.00
    a05	Otasi	mar	1	1920.00
    a06	Smith	feb	2	1700.50

<hr solid id=comments_below>


mj (Sep 17, 2001 7:28 pm; Comment #1)  --
 Q said he'd do this one.