Question5

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

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

Q said he'd do this one.