ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Problem with aggregate function

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with aggregate function

    Having problem with the following sql statement:

    Code:
    select customer.customer_nbr, last_name, first_name,
         rental.rental_ID, rental.copy_number, line_item.fee_type,
         fee_table.fee_amount, sum(fee_table.fee_amount)
    from person, customer, rental, line_item, fee_table
    where person.person_ID = customer.person_ID
         and customer.customer_nbr = rental.customer_nbr 
         and rental.rental_ID = line_item.rental_ID
         and line_item.fee_type = fee_table.fee_type
         and customer.customer_nbr = 21111
    group by rental.copy_number;
    
    
    
    </PRE>
    This worked:
    Code:
    select customer.customer_nbr, last_name, first_name,
         rental.rental_ID, rental.copy_number, line_item.fee_type,
         fee_table.fee_amount
    from person, customer, rental, line_item, fee_table
    where person.person_ID = customer.person_ID
         and customer.customer_nbr = rental.customer_nbr
         and rental.rental_ID = line_item.rental_ID
         and line_item.fee_type = fee_table.fee_type
         and customer.customer_nbr = 21111;
    And gave these results:

    CUSTOMER_NBR LAST_NAME FIRST_NAME RENTAL_ID COPY_NUMBER FEE_TYPE FEE_AMOUNT
    ------------ -------------------- -------------------- ---------- ----------- ---------- ----------
    21111 Smith John 111 1 Rental 5
    21111 Smith John 111 1 Late 2
    21111 Smith John 111 1 Rewind 1
    21111 Smith John 112 2 Rental 5
    21111 Smith John 113 3 Rental 5
    21111 Smith John 113 3 Rewind 1

    </PRE>
    </PRE>
    http://www.linkedin.com/in/chippermiller

  • #2
    Re: Problem with aggregate function

    Not sure (i'm not good with SQL) but looks like you've to GROUP BY all of these:
    customer.customer_nbr, last_name, first_name, rental.rental_ID, rental.copy_number, line_item.fee_type, fee_table.fee_amount

    since the sum() is applied to that group.
    â??No bird soars too high if he soars with his own wingsâ?? â?? William Blake

    Comment


    • #3
      Re: Problem with aggregate function

      You need to specifiy all columns without aggregate function in the group by clause.

      Birgitta

      Comment


      • #4
        Re: Problem with aggregate function

        Originally posted by B.Hauser View Post
        You need to specifiy all columns without aggregate function in the group by clause.

        Birgitta
        Thanks, Birgitta!!! -- I'm getting closer. (Have spent several hours trying to get this to work.)

        So.. now it looks like this:
        Code:
        select customer.customer_nbr, last_name, first_name,
             rental.rental_ID, rental.copy_number, line_item.fee_type,
             fee_table.fee_amount, sum(fee_table.fee_amount)
             from person, customer, rental, line_item, fee_table
        where person.person_ID = customer.person_ID
             and customer.customer_nbr = rental.customer_nbr
             and rental.rental_ID = line_item.rental_ID
             and line_item.fee_type = fee_table.fee_type
             and customer.customer_nbr = 21111
        group by customer.customer_nbr, last_name, first_name,
             rental.rental_ID, rental.copy_number, line_item.fee_type,
             fee_table.fee_amount;
        And the results are:
        CUSTOMER_NBR LAST_NAME FIRST_NAME RENTAL_ID COPY_NUMBER FEE_TYPE FEE_AMOUNT SUM(FEE_TABLE.FEE_AMOUNT)
        ------------ -------------------- -------------------- ---------- ----------- ---------- ---------- -------------------------
        21111 Smith John 113 3 Rewind 1 1
        21111 Smith John 111 1 Rental 5 5
        21111 Smith John 111 1 Late 2 2
        21111 Smith John 112 2 Rental 5 5
        21111 Smith John 111 1 Rewind 1 1
        21111 Smith John 113 3 Rental 5 5

        The last thing I'm stuck on is how to get the total for the "copy number" -- ex. for copy#1 its $5 for the rental, $2 for the late and $1 for the rewind so that's $8 altogehter. Copy #2 is $5 and copy #3 is $6.
        http://www.linkedin.com/in/chippermiller

        Comment


        • #5
          Re: Problem with aggregate function

          Looks like you want the sum to be per copy number.
          So, remove the fee type and amount from the group by.
          Also, remove them from the select clause.
          â??No bird soars too high if he soars with his own wingsâ?? â?? William Blake

          Comment

          Working...
          X