Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: trouble counting SUM GROUPed BY

    I've got payments table for estate agency and I want display Properties and total ammount was payed for each property
    SELECT PROPERTYID, PROPERTYNAME, TENANT, SUM(PAYMENTAMOUNT) FROM PAYMENTS GROUP BY PROPERTYID;
    and it doesn't work.
    How can I do it?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You need to add ALL aggregated columns to the GROUP BY:
    Code:
    SELECT PROPERTYID, PROPERTYNAME, TENANT, SUM(PAYMENTAMOUNT)
      FROM PAYMENTS
     GROUP BY PROPERTYID, PROPERTYNAME, TENANT;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112
    SELECT PROPERTYID, PROPERTYNAME, TENANT, SUM(PAYMENTAMOUNT) FROM PAYMENTS GROUP BY PROPERTYID,PROPERTYNAME,TENANT;

    All colums must be in group by clause
    Or
    select a.propertyid, propertyname, tenant from payments b, (a.totalselect propertyid, sum(paymentamount) total from payments a group by propertyid)
    where a.propertyid=b.propertyid

    Hope this help you

  4. #4
    Join Date
    Oct 2004
    Posts
    12
    what does a.totalselect means?

  5. #5
    Join Date
    Oct 2004
    Posts
    12
    Quote Originally Posted by LKBrwn_DBA
    You need to add ALL aggregated columns to the GROUP BY:
    Code:
    SELECT PROPERTYID, PROPERTYNAME, TENANT, SUM(PAYMENTAMOUNT)
      FROM PAYMENTS
     GROUP BY PROPERTYID, PROPERTYNAME, TENANT;
    But I want it to be grouped by propertyID only and in the same time see other fields.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:
    Code:
    SELECT P.PROPERTYID, P.PROPERTYNAME, P.TENANT, T.TOTAMT
      FROM PAYMENTS P
         , (SELECT PROPERTYID, SUM(PAYMENTAMOUNT) TOTAMT
              FROM PAYMENTS
             GROUP BY PROPERTYID) T
     WHERE P.PROPERTYID = T.PROPERTYID;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •